convert timeformat with 21h as 00h

[felipesewaybric]

Hi guys, i have this search:

| dbquery PROD-UOL7-MANUT-MONITORACAO
"select
dat_collect_transaction as \"data\",
dat_update as \"update\",
idt_inscription_account as \"conta\"
from collect_transaction
where idt_payment_method = 221 and dat_collect_transaction > sysdate -15 and dat_collect_transaction < sysdate-1"
| eval media=update-data
| convert timeformat="%Y/%m/%d %H:%M:%S" ctime(data) ctime(update)
| convert timeformat="%H:%M:%S" ctime(media)

but this is what return:
2015/06/21 10:11:33 2015/06/21 10:51:05 31298717 21:39:32
2015/06/21 10:12:34 2015/06/21 10:12:40 41224153 21:00:06

I dont understand why get set to 21h as 00h, anyone have this issue?

[richgalloway · Accepted solution]

There seems to be something a little off in your example returns. The two timestamps should have differences much smaller than the values shown. On my system, the search you provided yields a media value of 2372 seconds which equates to Thu, 01 Jan 1970 00:39:32 GMT. The ctime command is converting that time to your local timezone which is why you see 21 instead of 00.

As a workaround, format the difference 'manually':

... | eval media=update-data | eval min=floor(media/60) | eval secs=media%60 | eval media=min.":".secs | ...

[felipesewaybric]

| dbquery PROD

"select dat_collect_transaction as \"data\", dat_update as \"update\", idt_inscription_account as \"conta\" from collect_transaction where idt_payment_method = 221 and dat_collect_transaction > sysdate -15 and dat_collect_transaction < sysdate-1"
| eval intervalo=update-data
| convert timeformat="%Y/%m/%d" ctime(data)
| eval Date=strftime(strptime(data,"%Y/%m/%d"), "%d/%m/%Y")
| eventstats avg(intervalo) as Intervalo
| eval Intervalo = Intervalo*2
| stats count(eval(intervaloIntervalo)) as "Acima do tempo medio" by Date
| eval data_sort = strftime(strptime(Date,"%d/%m/%Y"), "%m/%d")
| sort 15 - data_sort | reverse
| table Date, "Acima do tempo medio", "Dentro do tempo medio"