All Apps and Add-ons

No columns listed when choosing a column as timestamp

Explorer

I am in the process of creating a new input in DBConnect. After running my SQL query which returns correct column, I then attempt to select a column as a timestamp, which is "Date Reported". "Date Reported" is in this format, 2017-12-15 08:39:23.0.

However DB Connect fails to identify any columns and returns "no matches" under the column drop down.

SQL Query is:
use osmq
SELECT "Date Reported" AS Date_Reported FROM Tickets_IT

Please could anyone advise!

Thanks

Super Champion

what version of db connect do you have and what type of db? postgres? mysql? can you try to put the timestamp in epoch? something like this: round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported ?
http://docs.splunk.com/Documentation/DBX/3.1.1/DeployDBX/Createandmanagedatabaseinputs

0 Karma

Explorer

DB Connect Version - 3.1.1
DB Type - MS-SQL Server Using MS Generic Driver

Where would the round(("Date Reported" -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported fit into my query? Not experienced with SQL.

Thanks

0 Karma

Super Champion

it would be in the select statement.

SELECT "Date Reported" AS Date_Reported, round(("Date Reported"  -DATE '1970-01-01' ) * 86400,0) as epoch_date_reported FROM Tickets_IT
0 Karma

Explorer

Thanks - just tried this & get following error message:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '1970-01-01'.

0 Karma

Super Champion

sorry, that was originally for oracle sql. try something more like DATEDIFF(SECOND,{d '1970-01-01'}, "Date Reported") there are slight differences with different sql database syntaxes, so googling can help you if this doesn't work, as i'm not the most familiar with ms-sql.

0 Karma

Explorer

That query works but hasn't resolved the issue of the issue of being unable to select a column for the timestamp.

Thanks

0 Karma