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

[BigCosta]

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?

[BigCosta · Accepted solution]

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

[richgalloway]

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

[cmerriman]

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.

[BigCosta]

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.

[cmerriman]

add this into your SQL statement:

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

[BigCosta]

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

[bjbrake]

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,