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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...