All Apps and Add-ons

Wrong date/time conversion from epoch

tomschoute
Explorer

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!

henchrm
Explorer

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.

0 Karma

henchrm
Explorer

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

0 Karma

henchrm
Explorer

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).

0 Karma

markthompson
Builder

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.

0 Karma

tomschoute
Explorer

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.

0 Karma

jberd126
Path Finder

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

0 Karma

PPape
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...