Splunk Search

DB Connect Convert Epoch to DateTime

justinfranks
Path Finder

Hello,

I have an MySQL database and I am trying to index some data from it. I can connect with no problems and I can index the data if I use:

select * from <table>

However, the date field is stored as an INT.

I have tried the following code but it doesn't work.

select d.productid, d.amount, o.orderid, cast(o.`date` as datetime)
from xcart_orders o, xcart_order_details d  
where o.orderid = d.orderid {{ and o.$rising_column$ > ? }}

How would I convert from Unix Epoch time store as an INT to something that Splunk will recognise as DATETIME? Normally, in SQL I would use DATEADD(); but Splunk doesn't seem to recognise that command.

0 Karma

jsilverbears
Path Finder

I am seeing this too. You can use the FROM_UNIXTIME(o.date) to get the UTC readable version of the int. If you want to convert that to your timezone, I am not sure how to do that when just pulling the data in with MySQL since DB Connnect 2, the one I am using will not allow a query with CONVERT_TZ to actually be used and return results (this is true at the time I am typing this an may be corrected later). You can however do that after the fact in the Splunk queries.

You probably have already figured this out but I thought it may be helpful for anyone else seeing this.

0 Karma

harshavrath
Contributor

Hi,

If your rising column field is your date field that you are mentioning then its not possible.

If its not then you can use

select to_char(date_attribute,'YYYY-MM-DD HH24:MI:SS') date_attribute,id from table_name {{WHERE $rising_column$ > ?}}

0 Karma

richgalloway
SplunkTrust
SplunkTrust

When you say "it doesn't work", what error are you seeing?

Have you looked at this answer? http://answers.splunk.com/answers/126547/db-connect-epoch-timestamp

---
If this reply helps you, Karma would be appreciated.
0 Karma

justinfranks
Path Finder

Hi,

When running that exact command without the rising column bit, i get:

productid   amount  orderid cast(o.`date` as date time)

with no data in the cast column.

Unfortunately, Splunk is not recognising the date when it gets indexed, so the "_time" field is when the data was indexed. This is why I am trying to convert it before it is indexed.

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...