I have a table ‘t’ with date(yyyy-mm-dd), hour(1-12), minute(00-59), ampm(a/p), and timezone(pst/est) fields.
How can I select the rows that are <= now()?="" (ie.="" already="" happened)
Thank you for your suggestions!
edit: this does it without attention to the hour/minute/ap/tz fields:
SELECT * FROM t.date WHERE date <= now()
Here's one way to do it - combine all your seconds, minutes, etc into a date and compare to
NOW(), making sure you do the comparison in the same time-zone. (Untested):SELECT * FROM t LEFT JOIN y ON t.constant=y.constant WHERE CONVERT_TZ(STR_TO_DATE(CONCAT(date,' ',hour,':',minute,' 'ampm), '%Y-%m-%d %l:%i %p' ), timezone,"SYSTEM") < NOW();
If your hour is 01 - 12 not 1-12 then use
tries to stick your date and time columns together and convert them into a date.
converts this date from whatever timezone is specified in the
column to system time.
This is then compared to
, which is always in system time.
As an aside, perhaps you should make a single column
using MySQL's date datatype, as it's a lot easier to do arithmetic on that!
For reference, here
is a summary of very useful mysql date functions where you can read up on those featuring in this answer.