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
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>
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
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>
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