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

[BobKimata]

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

[woodcock]

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>

[BobKimata]

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

[woodcock]

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>

[BobKimata]

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