All Apps and Add-ons

Problem dealing with epoch values - splunk does not recognize the date format and gives wrong dates.

New Member

I am trying to index a date column of my table which is stored in epoch format.
No matter what i do, when i try to view the index, it gives me wrong value.
Ex : if the event had occured at 14:22 on Aug 8th, the time column gives me the current time(i.e sysdate) and the event column gives me "1970-01-01 05:29:59" as the standard value, no matter what conversions i do in my SQL query .

the output timefomat i have set to :

My DB is in PDT timezone.

Different flavors of SQL query used :

FROM (SELECT TO_CHAR (NEW_TIME (epoch_to_date (a.created), 'GMT', 'PDT'),'YYYY-MM-DD HH24:MI:SS') create_date,a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type,a.server_stats_id FROM temp_sg_server_stats a)

--Raising column = create_date

SELECT * FROM (SELECT TO_CHAR(epoch_to_date_for_tz (a.created),'YYYY-MM-DD HH24:MI:SS') createdate,a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type, a.server_stats_id FROM temp_sg_server_stats a) {{WHERE $rising_column$ > ?}}

Raising column = create_date

and when i issue the query without any coversions, and index it on a.created directly, it gives me the same result as well.

And to add upon it,
i added the following in props.conf.
TZ = US/Pacific

and in inputs.conf, i added these two columns.
host = perf70
index = try_props_epoch
output.format = kv
output.timestamp = 1
output.timestamp.column = created_date
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
query = SELECT *\r\n FROM (SELECT TO_CHAR (NEW_TIME (epoch_to_date (a.created), 'GMT', 'PDT'),\r\n 'YYYY-MM-DD HH24:MI:SS'\r\n ) created_date,\r\n a.cobrand_id, a.cache_item_id, a.sum_info_id, a.server_type,\r\n a.server_stats_id\r\n FROM temp_sg_server_stats a) {{WHERE $rising_column$ > ?}}
sourcetype = monitor_server_stats_epoch_conversion
tail.rising.column = created_date

Experts, please provide a solution for this.

Tags (2)
0 Karma

New Member

finally got the resolution after a lot of P&Cs.

in the query gave the epoch value as epoch_to_date_for_tz(column name) alias name.
Raising column = alias name.

-added the following :
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss

-add the following :
TIME_FORMAT="%Y-%m-%d %H:%M:%S"

Restarted splunk and boom - it started working like a charm 🙂 🙂

0 Karma

New Member

thanks for the feed back and i tried both the suggested ways, but no luck 😞

1) directly indexing the epoch field.
o/p - i get time as today's time and event date as 01-01-1970 5:59:59

2) specified UTC in the props.conf instead of US/Pacific
o/P - got the same as above.

Used one more thing, but it isn't working.
was trying to issue the raising column as "to_char(epoch_to_Date_for_tz(CREATED),'YYYY-MM-DD HH24:MI:SS')" but getting an exception and it is not allowing me to create the database input.

Our DB epoch value is a digit integer like - 1407853860,1407853860 etc..
please lemme know if i am missing anything here.

0 Karma


Epoch is normally given in UTC. Looking at your props you specify US/Pacific. Within your props.conf specify UTC or GMT.

About Epoch Time

0 Karma


Hi, you say you have a timestamp column in your table which is in epochtime format, have you simply tried to use this field as the timestamp field ? Splunk should automatically recognize the epochtime as the timestamp format

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...