DB Connect Convert Epoch to DateTime

[justinfranks]

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.

[jsilverbears]

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.

[harshavrath]

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$ > ?}}

[richgalloway]

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

[justinfranks]

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.