Splunk Search

How do I convert the time picker date into readable date formats?

Path Finder

Hey guys,

I have a dashboard table that populates from a SQL search query. The dates in the database are in a normal readable format ie 2015-07-18. I have put a time picker which I want to enable me execute the query when a user selects a date range from the date time picker. I have realized the dates in the time picker are in this format: 1437364800

How do I convert this date into a normal time format before it executes in the query? As it is, I don't get any results since I don't have such dates (1437364800 ) in my database. I would like to execute something like this:

<query>
  | dbquery AdWordsROI limit=1000 "select * from account_performance where `Day` between $time_range1.earliest$ and $time_range1.latest$"
</query>

where time_range1.earliest and time_range1.latest are the dates I need to convert.

Regards
Bob

0 Karma

Esteemed Legend

Try this:

<query>
  | dbquery AdWordsROI [| noop | stats count | convert timeformat="%Y-%m-%d" ctime($time_range1.earliest$), ctime($time_range1.latest$) | eval sql_str= "select * from account_performance where Day between '" . $time_range1.earliest$ . "' and '" . $time_range1.latest$ . "'" | return $sql_str ]
</query>
0 Karma

Path Finder

Hey.
I tried that but got the error:

Error in 'eval' command: The operator at '1438056000 "' and '" 1438660800 "'"' is invalid

Managed to play around with SQL and got it working, however it didn't work for the presets e.g. last 7 days etc. It works well only when a user selects Date Range and chooses the the between dates.

| dbquery AdWordsROI "SELECT * FROM account_performance  WHERE`Day` between from_unixtime($time_range1.earliest$,'%y-%m-%d')and from_unixtime($time_range1.latest$,'%y-%m-%d')"

I receive this error when I select Last 7 Days

command="dbquery", A database error occurred: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@h,'%y-%m-%d')and from_unixtime(now,'%y-%m-%d') group by ClientName' at line 1

basically the earliest and latest time in this case isn't epoch time. What do I need to do?

from_unixtime is an SQL function that returns normal time from epoch time

Thanks
Bob

0 Karma

Esteemed Legend

Maybe this would work, too put the times in double-quotes also, because it is in xml, not in the search bar:

<query>
   | dbquery AdWordsROI [| noop | stats count | convert timeformat="%Y-%m-%d" ctime($time_range1.earliest$), ctime($time_range1.latest$) | eval sql_str= "select * from account_performance where Day between $time_range1.earliest$ and $time_range1.latest$'" | return $sql_str ]
</query>
0 Karma

Path Finder

I am trying out this but it doesnt seem to be working either. Where am I going wrong?

<query>
               | dbquery AdWordsROI [ | stats count | head 1  | addinfo | convert timeformat="%Y-%m-%d" ctime(time_range1.earliest), ctime(time_range1.latest)|eval sql_str= "select * from account_performance where Day between '$time_range1.earliest$' and '$time_range1.latest$'"  | return $sql_str 
</query

time_range1 is the token from the time picker. I end up getting the following error:
command="dbquery", A database error occurred: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!