MySQL Select the rows & lt; = Now (), using separate time fields

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 %h
instead of %l
in the STR_TO_DATE
.

The STR_TO_DATE
tries to stick your date and time columns together and convert them into a date.

The CONVERT_TZ(...,timezone,"SYSTEM")
converts this date from whatever timezone is specified in the timezone
column to system time.

This is then compared to NOW()
, which is always in system time.

As an aside, perhaps you should make a single column date
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.

Good luck!

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » MySQL Select the rows & lt&semi; &equals; Now &lpar;&rpar;&comma; using separate time fields

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录