topic Re: Wrong date/time conversion from epoch in All Apps and Add-ons

Wrong date/time conversion from epoch

tomschoute — Mon, 28 Sep 2020 19:55:13 GMT

I get data from an SQL Server db via DB input (DB Connect v2) in my index.
I have a date field in epoch format from the db that I use as timestamp field.

When I search the index it seems like the date conversion is wrong sometimes. For instance see below result.

Time
5/12/15 4:56:47.000 PM

Event
"12-29-2015 16:56:47" object_id=281487347683737, id=3947, status="COMPLETED", status_object_id=3094610094, created=1419868607970

The column "created" comes from the database and should be converted. The date/time between quotes is the converted date which in this case has a wrong year (it should be 2014). The Time part is set to the time the data was loaded I think.

To compare, another set of data gave below result:

Time
5/19/15 9:30:04.000 AM

Event
"11-18-2009 09:30:04" object_id=281485922926681, id=32, status="CLOSED", status_object_id=3094610096, created=1258533004970

The "created" datetime and the datetime it was converted to are the same (11-18-2009 09:30:04) but the "TIme" field is set to today. This Time field is used to create separate year, month, etc. fields which I want to use to create a chart on.

There are also a lot of results that give the correct values, for example:

Time
11/5/14 11:22:58.000 AM
Event
"11-05-2014 11:22:58" object_id=281487302656167, id=3800, status="DISPATCHED", status_object_id=3120234513, created=1415182978970

Does anyone has an idea what is going wrong here? How these wrong conversions can be avoided because they make the data unreliable.
Or maybe explain how I can do it differently? To be clear: I want to make some charts on data from a database and I want to use a date field from the database to create counts per year, month and so on.

Thanks a lot!

Re: Wrong date/time conversion from epoch

PPape — Mon, 28 Sep 2020 19:55:16 GMT

Hi tomschoute,

maybe you should take a look at this:
props.conf

I think the MAX_DAYS_AGO parameter is the one you need.
I had the same issue one time and it solved it for me.

Re: Wrong date/time conversion from epoch

markthompson — Tue, 19 May 2015 14:08:39 GMT

I'm confused as to what the problem is here.

Your data, Splunk is picking up the created as being the timestamp. The only issue you might have could be timezones, for example, BST might be adding + 1 hour.

Re: Wrong date/time conversion from epoch

tomschoute — Tue, 19 May 2015 14:30:51 GMT

At this moment I only have the problem I mention first.
I get a date value in epoch format. If I convert that on a website it gives the correct date (12-29-2014) but in splunk it is converted to "12-29-2015".
I have no clue yet what causes that addition of a year. Maybe timezone or something but it goes correct for over 4000 records and goes wrong for 6. I am under the impression that it might have something to do with my data and probably not with splunk but I want to understand why.

Re: Wrong date/time conversion from epoch

jberd126 — Wed, 20 May 2015 19:05:16 GMT

Regarding the wrong year I ran into this problem yesterday, I think it's a bug as it's assuming this year for the timestamp and not last year.

Specifically:

"2015-12-30 13:57:30" timestamp=1419973050000

Where timestamp converts:

GMT: Tue, 30 Dec 2014 20:57:30 GMT
Your time zone: 12/30/2014, 1:57:30 PM GMT-7:00

Re: Wrong date/time conversion from epoch

henchrm — Tue, 24 Nov 2015 15:09:44 GMT

UPDATE (workaround)
I have changed my Splunk DB Connect - DB Input to use 'epochtime' format as its conversion (rather than the default human readable format.)

This problem then does not occur. This seems to be a reasonable workaround (for me at least).

Original Response
I've just had exactly the same problem... reading in data from 2011-2015, the ONLY date effected was 29th December 2014, where ALL epoch times for this date have been converted to have the year 2015....

I expect this is a problem with the "splunk db connect" app rather than splunk itself.

Re: Wrong date/time conversion from epoch

henchrm — Tue, 24 Nov 2015 16:26:23 GMT

I've done more investigation, and actually found that this happens on the last few days on each year within the data. My assumption therefore is that the splunk DB connect app is calculating the year incorrectly from the epoch format timestamp before indexing. (As the event string itself has the converted date, which is incorrect - and the splunk event time always matches this converted date).

e.g.

Event Time
31/12/2014
15:18:27.000

Event Data
"2014-12-31 15:18:27" CHANGEID=1770752, CHANGETS=1388503107000

Where 1388503107000 should be GMT: Tue, 31 Dec 2013 15:18:27 GMT

Re: Wrong date/time conversion from epoch

henchrm — Mon, 30 Nov 2015 11:33:35 GMT

I have changed my Splunk DB Connect - DB Input to use 'epochtime' format as its conversion (rather than the default human readable format.)

This problem then does not occur. This seems to be a reasonable workaround (for me at least).