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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...