topic Re: Epoch Time to Conventional Time in Splunk Search

Epoch Time to Conventional Time

linu1988 — Mon, 28 Sep 2020 14:31:49 GMT

Hello,
I am having trouble converting to Hour:Minute:Second format from epoch time

First i have made a subtraction which i want it to be in human readable format. But it's giving me wrong result. Below is my query.

|Stats max(_time) as LastBackup,min(_time) as StartBackup,sum(FileSize) as TotalBackupSize | eval TimeDiff=LastBackup-StartBackup| table StartBackup LastBackup TimeDiff|eval Total=strftime(TimeDiff,"%H:%M:%S")

StartBackup LastBackup  TimeDiff    Total

1 1375920004 1375929896 9892 04:44:5

But the value should be 2:44:5, Why am i getting such wrong result?

Any insight will be much appreciated.

Re: Epoch Time to Conventional Time

MuS — Thu, 08 Aug 2013 12:03:24 GMT

Hi linu1988,

once did some epoch to human readable converting using this commands:

  convert timeformat="%Y-%m-%d %T" mktime(Message_time) | fieldformat Message_time=strftime(Message_time,"%Y-%m-%d %T")

adapted to your search it should be something like this:

 | stats max(_time) as LastBackup,min(_time) as StartBackup,sum(FileSize) as TotalBackupSize | eval TimeDiff=LastBackup-StartBackup | fieldformat TimeDiff=strftime(TimeDiff,"%T") | table StartBackup LastBackup TimeDiff

does that make sense?

Cheers, MuS

Re: Epoch Time to Conventional Time

kristian_kolb — Thu, 08 Aug 2013 12:34:33 GMT

The reason for the 'wrong' result is that you asked for a conversion of the epoch 9892, which occurred on January 1st, 1970, at 02:44:52. That is quite different from a generic duration in seconds. However, since you only present the H:M:S part of the result, that may not be obvious at first glance. Could it be that you are two timezones east of London? That could explain the two extra hours.

Re: Epoch Time to Conventional Time

kristian_kolb — Thu, 08 Aug 2013 12:40:35 GMT

That correct? From the docs on convert:

mktime()
Syntax: mktime()
Description: Convert an human readable time string to an epoch time. Use timeformat option to specify exact format to convert from.

If the docs are right, you'd be trying to convert epoch (treated as %T) into epoch.

Re: Epoch Time to Conventional Time

MuS — Thu, 08 Aug 2013 12:49:25 GMT

hehe, copy paste error - updated the answer 🙂

Re: Epoch Time to Conventional Time

linu1988 — Thu, 08 Aug 2013 12:54:34 GMT

Now this comment make some sense, I got the point. On the timezone we have splunk server and The actual servers on the same timezone. How do i get the actual difference of the time on the event's the question as it's 2:44 not 4:44 minutes

Re: Epoch Time to Conventional Time

linu1988 — Thu, 08 Aug 2013 13:15:06 GMT

Hey MuS,
My query wasn't that different from yours. It gave me the same answer i was getting. I am not confident why the time difference is coming like this, but having so many great functionality in Splunk this small things should be coming automatically rather this much thinking. At last i ended up with below:

May look dirty but i am at least getting the result

Re: Epoch Time to Conventional Time

kristian_kolb — Thu, 08 Aug 2013 20:34:50 GMT

Yes, Splunk and the source server may be in the same TZ, but epoch is always in UTC, which is why I figured that you may be in central Europe. With daylight saving, CET is two hours ahead of UTC.

So the 2:44 (or 4:44) is not duration expressed as H:M or M:S, but rather the actual time.

Re: Epoch Time to Conventional Time

siraj198204 — Mon, 28 Sep 2020 17:43:04 GMT

source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(Lastpasswordchangedate, "+90d@d"), "%Y/%m/%d %H:%M") | eval exp_days=floor((exp_date-Lastpasswordchangedate)/86400) |rename lastPasswordChange AS Lastpasswordchangedate

This is the query ,

Lastpasswordchangedate =2014-08-06 11:20:39
application =TFAYD
exp_date =2004/07/07 00:00
owner =206404613
refreshedDate =2014-09-26 09:15:25
sourcetype =mysql

i need exp_date output 90 days from Lastpasswordchangedate , but iam getting wrong output . any one can help me on this ...