I am trying to make a query about joining showingdates and showings table and get all the showings for a date and order them by time.
My showingdates dayfield is formed as showdate(datetime) and shows "2013-03-06 00:00:00"
My showings performance_date field is again a datetime and examples are
"2013-03-06 20:00:00 ,
"2013-03-06 21:00:00" .
How can I make a query for that date and order them by time ?
This should work.
SELECT garbage
FROM trash
WHERE DATE(showdate) = '2013-03-06'
ORDER BY TIME(showdate) DESC
The time here is descending. Remove DESC to achieve ascending order.
To get rows only from a specific date (no use of any function on the showdate column, so indexes can be used):
WHERE showdate >= '2013-03-06'
AND showdate < '2013-03-06' + INTERVAL 1 DAY
To order by time (since all rows will have the same date, and the showdate has both date and time, simply order by that):
ORDER BY showdate