Getting Data In

convert timeformat with 21h as 00h

felipesewaybric
Contributor

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?

Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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 | ...
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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 | ...
---
If this reply helps you, Karma would be appreciated.

felipesewaybric
Contributor

| 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"

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...