Splunk Search

How to convert epoch time to human readable format in search query?

ziyod2005
Explorer

Could someone please help me convert epoch time to human readable time?

"time":1407361408100

this is what i'm trying to get "time":"Wed, 06 Aug 2014 21:43:28"

Tags (3)
1 Solution

strive
Influencer

Try this

sourcetype=test | eval c_time=strftime(log_time,"%m/%d/%y %H:%M:%S") | table _time, c_time

the function strftime(X,Y) takes an epochtime value, X, as the first argument and renders it as a string using the format specified by Y.

View solution in original post

nick405060
Motivator

Downvoted. Considering converting from epoch is one of the most common Splunk questions of all time, considering this page has 46k views, and considering that each and every answer is entirely incorrect (and the actual question itself is misleading) this page is desperately in need of removal.

1) The question doesn't actually provide a standard epoch time. A millisecond epoch time is provided
2) The answer with 16 votes (?????) fails to divide by 1000 OR provide the correct format
3) The answer with 3 votes (?????) fails to provide the correct format

@somesoni2's comment of "%a,%d %b %Y %H:%M:%S"is correct, although technically you need to divide by 1000 if you are to use the millisecond epoch time that the post provides. 99% of people who find this page are merely looking to convert epoch time to the default Splunk human-readable format, in which case what they are looking for is barely on this page. They are most likely looking for "%Y-%m-%d %H:%M:%S" which is mentioned nowhere, or possibly "%F %T" as mentioned in the comments.

I've been told that the initial question has not been retroactively edited in any way which begs the question of what happened???? I understand comments from a comment chain were likely converted to answers without the correct context, but still. Part of the problem is that, in the comment chain, the parameters surrounding the initial question were changed by the asker. Smh. This is a giant mess.

@mstjohn_splunk

vijaysubramania
Path Finder

Thats Correct.

|eval start=strftime(viewingPeriodStart/1000,"%a,%d %b %Y %H:%M:%S")

|eval end=strftime(viewingPeriodEnd/1000,"%a,%d %b %Y %H:%M:%S")

Also, How do i find difference between 2 times in hrs?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@vijaysubramania 

Difference between two second granularity epoch times is simple

 

| eval diff=end-start

 

that gives you seconds, then you do with that as you want. Don't use time formatting functions as they will take account of your time zone, but it's simple to do the maths

 

| eval hours=floor(diff/3600)
| eval minutes=floor((diff % 3600)/60)
| eval seconds=diff % 60

 

 

vijaysubramania
Path Finder

@bowesmana 

Works like a charm. I didn't think of applying basic math skills 🙂

|eval start=strftime(viewingPeriodStart/1000,"%a,%d %b %Y %H:%M:%S")
|eval end=strftime(viewingPeriodEnd/1000,"%a,%d %b %Y %H:%M:%S")
|eval diff=viewingPeriodEnd/1000-viewingPeriodStart/1000
|eval hours=floor(diff/3600)
|eval minutes=floor((diff % 3600)/60)
|eval seconds=diff % 60

 

0 Karma

Dev_Choudhary
Path Finder

Try this
| eval Time=strftime(log_time_field/1000, "%d-%m-%Y %H:%M:%S")

ziyod2005
Explorer

Here is my actual query

source = "*.job" | eval c_time=strftime(time,"%m/%d/%y %H:%M:%S") | table time, c_time

time field does exist and that's the field that I'm trying to convert

0 Karma

daivish
Explorer

@ziyod2005 -- Can you post the correct Answer if you ever got the solution on this problem?

0 Karma

somesoni2
Revered Legend

Issues that I see.

1) in the timeformat there is an extra space. Remove that
2) The field name used in ctime need to be verified. Do you have a field called log_time apart from _time field??

0 Karma

strive
Influencer

Try this

sourcetype=test | eval c_time=strftime(log_time,"%m/%d/%y %H:%M:%S") | table _time, c_time

the function strftime(X,Y) takes an epochtime value, X, as the first argument and renders it as a string using the format specified by Y.

landen99
Motivator

call me lazy, but ..

eval c_time=strftime(log_time,"%F %T")

mcronkrite
Splunk Employee
Splunk Employee

^^^ This is the answer!
eval c_time=strftime(log_time,"%F %T")

0 Karma

daivish
Explorer

@ziyod2005 Can you post your resolution here ? As i have similar issue... None of posted answers helping me to resolve my issue. Did you ever got your resolution on this question ?

0 Karma

mmensch
Path Finder

You have to see what units your epoch time value is in. If it is not working, try dividing the number by 1000 first. 🙂

Example:

|eval log_time=log_time/1000 |eval c_time=strftime(log_time,"%F %T") | table log_time, c_time

vijaysubramania
Path Finder

This Works. How do I calculate if I want to see the difference between 2 epoch times and displayed in hrs

0 Karma

arungeorge09
Path Finder

I am unable to get this working too. I tried all the options and unable to see date in human readable format.

0 Karma

ziyod2005
Explorer

I think we're getting close 🙂
1406263182098 Fri,31 Dec 9999 23:59:59
1406263177094 Fri,31 Dec 9999 23:59:59

kristian_kolb
Ultra Champion

Milliseconds to blame? %3N

0 Karma

somesoni2
Revered Legend

Use "%a,%d %b %Y %H:%M:%S" instead of "%m/%d/%y %H:%M:%S %Z".

ziyod2005
Explorer

Here's what I'm getting
1406263182098 12/31/99 23:59:59

Instead I should be getting:

GMT: Fri, 25 Jul 2014 04:39:42 GMT

0 Karma

Supriya
Path Finder

Hi,

I'm looking for the answer for the question you posted,

Do you find any answer for this?

0 Karma
Get Updates on the Splunk Community!

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...

Adoption of Infrastructure Monitoring at Splunk

  Splunk's Growth Engineering team showcases one of their first Splunk product adoption-Splunk Infrastructure ...