Converting a timestamp, but the hour always becomes: 00

[RB5]

Hi, I was hoping for help on this. I want to reformat a date as follows (and if there is an easier--more condense way--I'm all ears):

With 'date_month'
being the full month name, I want a final date of the form, for example: 2013-10-18-15.00.00

BUT, I always end up with the hour being: 00, for example: 2013-10-18-00.00.00

I am using:

index . . . | stats sum(YYY) by date_year date_month date_mday date_hour | eval datestr=date_year."-".date_month."-".date_mday."-".date_hour
| eval datestr2 = strftime( strptime( datestr, "%Y-%B-%d-%H" ), "%Y-%m-%d-%H.00.00") | table datestr2 sum(YYY)

I can see datestr is: 2013-october-18-15 so it pulled the hour correctly.

I can see strptime is in 'epoch time' and maybe that is the reason, it is: 1382054400.000000

Thanks.

[sideview]

You're right by the way - your time is being extracted from an epochtime value and there's a longstanding bug in Splunk where it cannot determine timezone when it gets the time from an epochtime value. Instead of using local server time, or failing that not creating date_* fields at all, splunkd creates all the date_* fields as though the data was from the GMT timezone. So effectively date_* fields is always unreliable whenever _time is extracted from epochtime values. (unless you happen to use GMT on that host. 😃

[RB5]

Thanks to both somesoni2 and aelliott, both solutions work - and am glad one of them is 'condensed' so to speak. (FYI: I tried to award the maximum number of points for each but it looks like the field will only take 1 digit - so I put in a '9').

[aelliott]

does something like this not work?
index=* | eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | stats count(host) by Date

[somesoni2]

This should do the trick for you

index . . .  | stats sum(YYY) by date_year date_month date_mday date_hour 
| eval datestr=relative_time(strptime(date_year."-".date_month."-".date_mday , "%Y-%B-%d" ),"+".date_hour."h@h")
| eval datestr2 = strftime(datestr, "%Y-%m-%d-%H.00.00") 
| table datestr2 sum(YYY)

[RB5]

Thank you, but for what I put, you say it works? It does NOT have '00' for the hour, that is, it does NOT have: 2013-10-18-00.00.00 ? It has: 2013-10-18-15.00.00 ?

I should have added that I had already tried: strftime( _time, "%Y-%m-%d-%H.00.00") but I do not get anything back for the date:

index= . . . | stats sum(nrcpts) by date_hour date_mday
| eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | table Date sum(nrcpts)

[aelliott]

That works for me what you have above,
If you are looking to use the timestamp on the event, you could use
index= . . .| eval Date=strftime(_time, "%m/%d/%Y-%H.00.00") | stats count(host) by Date

[kristian_kolb]

or ... | timechart span=1h sum(YYY)

/k