topic Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format? in All Apps and Add-ons

Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

BigCosta — Mon, 22 May 2017 14:21:19 GMT

Hello!
I used Splunk DB Connect v1 and now I updated it to Splunk DB Connect v3. I get data from oracle sql.
I have several TIMESTAMP type columns that in version 1 were saved in the epoch format, but in version 3 they are stored in human readable format.

Example (CREATED is TIMESTAMP type):

Version 1: CREATED = 1495450599.159
Version 3: CREATED = 2017-05-22 13:56:39.159

How to make so that in version 3 TIMESTAMP type was in epoch format?

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

cmerriman — Mon, 22 May 2017 15:48:39 GMT

The best workaround I have for that is to just write an eval after I bring in my dbxquery. |eval CREATED=strptime(CREATED,"%Y-%m-%d %H:%M:%S.%3N")

you could also use convert. |convert mktime(CREATED) timeformat="%Y-%m-%d %H:%M:%S.%3N"
https://docs.splunk.com/Documentation/Splunk/6.6.0/SearchReference/Convert
There might be another way, but that's what I do.

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

BigCosta — Tue, 23 May 2017 02:37:32 GMT

Thanks for the quick response!
I need to be in the index TIMESTAMP was in epoch.
I already have an old index in which TIMESTAMP is in epoch and there are complex searches and applications that use epoch. In my situation, the use of this workaround is very difficult.

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

cmerriman — Tue, 23 May 2017 11:42:06 GMT

add this into your SQL statement:

trunc((CREATED-TO_DATE('01-01-1970','MM-DD-YYYY')) * 86400) as indexTime

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

BigCosta — Wed, 24 May 2017 04:26:57 GMT

After adding, I get an error:

SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

Here it works without error:

(trunc(CREATED) - TO_DATE('01-01-1970', 'MM-DD-YYYY')) * 86400 as indexTime

BUT time is rounded to one day. This is unacceptable to me. I need original accuracy.
In fact, DB Connect v3 converts a TIMESTAMP type to a DATATIME type.

Can I make DB Connect v3 not do this with TIMESTAMP types, but just keep it in the form in which it is shown in oracle (1495450599.159)?

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

BigCosta — Mon, 05 Jun 2017 10:32:54 GMT

I found a workaround for the problem.
I changed the SQL query

round((cast(CREATED as date)-to_date('01011970','MMDDYYYY'))*86400)||'.'||to_char(CREATED, 'FF') as CREATED_EPOCH

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

richgalloway — Mon, 05 Jun 2017 12:38:23 GMT

If your problem is resolved, please accept an answer to help future readers.

Re: Splunk DB Connect: How to save TIMESTAMP type fields in Epoch format, not in human readable format?

bjbrake — Tue, 29 Sep 2020 19:10:22 GMT

This worked for me:

trunc((extract(day from (CREATED - to_date('01-JAN-1970','DD-MON-YYYY')))*86400+extract(hour from CREATED)*3600+extract(minute from CREATED)*60+extract(second from CREATED))) as indexTime,