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?
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"
There might be another way, but that's what I do.
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.
add this into your SQL statement:
trunc((CREATED-TO_DATE('01-01-1970','MM-DD-YYYY')) * 86400) as indexTime
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)?
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,
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
If your problem is resolved, please accept an answer to help future readers.