Dataset: Get Interval

[yferreira]

Hi,

I was trying to figure out how I can subtract "from_dtime(dtime)" of "now()" and count the interval in days.

I was wondering if somebody knows.

Tks folks.

[hzhao_FTNT · Accepted solution]

I see, then pls try  to_char((now() - from_dtime(dtime)), 'DD') as interval

[hzhao_FTNT · Accepted solution]

Please try: 

select (case when  (date(now()) - date(from_dtime(dtime))) > 90 then 'More than 90 days' else to_char( (now() - from_dtime(dtime) ), 'DD') end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

regards,

hz

[hzhao_FTNT · Accepted solution]

then:

select (case when  (date(now()) - date(from_dtime(dtime))) < 90 then to_char( (now() - from_dtime(dtime) ), 'DD')  else 'More than 90 days' end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

[hzhao_FTNT]

Hi, we have predefined $day_of_month and $day_of_week, please check it out.

Regards,

hz

[yferreira]

I'm trying to do something like that:

select now() - from_dtime(dtime) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null ORDER BY interval DESC limit 1

But when I print "now()", I'm getting a date without timezone.

[hzhao_FTNT · Accepted solution]

I see, then pls try  to_char((now() - from_dtime(dtime)), 'DD') as interval

[yferreira]

Great!

It worked really fine.

[yferreira]

Another doubt...

How can I ignore "Time Period" ?

[hzhao_FTNT]

$filter contains device and time filter, do not recommend to ignore it.

[yferreira]

It's because I need to search for every single log message referring to the "Fortigate started", regardless of the "Time Period" setted.

Any ideas? 

[hzhao_FTNT]

Hi, I think you can select "custom..." for a longer time period.

hz

[yferreira]

Ok. The query looked like this:

select to_char( (now() - from_dtime(dtime) ), 'DD') as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

Is possible to create a condition, for example, if the Days be more than 90 days or N/A then print 'More than 90 days' instead of printing the number of the days? But if it less than 90 days, keep showing in Days.