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

存储架构 2017-11-15

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!

责编内容by:Hello, buddy! (源链)。感谢您的支持!

您可能感兴趣的

Oracle、mysql产品性能优化总结 Oracle、mysql产品性能优化总结 阿弥陀佛观音释迦莲师弥勒使者---神农紫薇太乙地藏 耶稣 火龙真人太极天师光明战神金刚梦大同的笨小孩 雷锋201...
个人订阅的10佳博客与相关介绍 前段时间,我在微博上分享了自己订阅的博客Feeds,一共有200个左右,内容覆盖多个领域,包括有:Database(MySQL、Oracle、PostgreSQ...
Accelerate mysql / mysql queries in django I use the following code to select popular news entries (by date) from the datab...
GET variables with spaces – they work&co... I have a PHP page where I'm passing the city name via a "city" URL/GET variable...
Limit MySQL results to earlier days of the week Mysql Convert date to days of the week I ha...