我有一个表,其中一列名为"date",类型为 DATETIME
。我需要选择其日期为一个月最后一分钟的所有行,例如:
31-12-17 23:59:00
30-11-17 23:59:00
如何在一个查询中实现此目的?
LAST_DAY
来获取该月的最后一天,并使用DATE_FORMAT
来获取比较时间。
SELECT * FROM <table_name>
WHERE DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")=DATE_FORMAT(<date_time_col>,"%d")
AND DATE_FORMAT(<date_time_col>,"%H:%i")='23:59';
详细说明:
因此,基本上,要获得正确的行,我们需要获取该月的最后一天和当天的最后一分钟
LAST_DAY
将有助于获取给定日期时间的月份的最后一天。DATE_FORMAT
将有助于获得日期。现在,我们将将它们组合在一起,以获得给定日期时间的最后日期。
DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")
如果该月的最后一天是29-02-2018
,则上面将返回29
。
现在,我们需要检查,给定的日期时间是否有一天的最后一分钟?我们可以再次利用DATE_FORMAT
从给定的日期时间中提取时间。在这里,我们将只关注小时和分钟(根据 OP 问题(。所以,它应该是
DATE_FORMAT(<date_time_col>,"%H:%i")
如果给定的日期时间为29-02-2018 23:59:00
,则上面将返回23:59
。
LAST_DAY
来获取每个月的最后一天:
SELECT LAST_DAY(mydate)
返回:
2031-12-31
2030-11-30
然后使用该STR_TO_DATE
以获取每个月最后一天的最后一分钟:
SELECT STR_TO_DATE(CONCAT(LAST_DAY(mydate),
' ',
'23:59:00'),
'%Y-%m-%d %H:%i:%s') AS last_min
返回:
last_min
--------------------
2031-12-31T23:59:00Z
2030-11-30T23:59:00Z
2030-11-30T23:59:00Z
现在,您可以使用last_min
与实际日期时间值进行比较。
如果要获取日期时间在上一分钟间隔内的记录,则可以另外使用DATE_ADD
来获取上述日期时间值的下一分钟:
SELECT DATE_ADD(last_min,
INTERVAL 1 MINUTE) AS next_min
返回:
next_min
---------------------
2032-01-01T00:00:00Z
2030-12-01T00:00:00Z
2030-12-01T00:00:00Z
使用上述表达式,您可以构建一个谓词,用于检查所需间隔内的日期。
在这里演示
SELECT * FROM table WHERE DATE(date) = LAST_DAY(date) AND HOUR(date) = 23 AND MINUTE(date) = 59;
由于此查询不会使用任何索引,因此在大型表中可能会很慢。
更有效的解决方案如下:
SELECT * FROM mytable WHERE mydate IN
(
'2017-01-31 23:59:00',
'2017-03-31 23:59:00',
'2017-04-30 23:59:00',
'2017-05-31 23:59:00',
'2017-06-30 23:59:00',
'2017-07-31 23:59:00',
'2017-08-31 23:59:00',
'2017-09-30 23:59:00',
'2017-10-31 23:59:00',
'2017-11-30 23:59:00',
'2017-12-31 23:59:00'
)
OR mydate = '2017-03-01 00:00:00'- INTERVAL 1 MINUTE;