时间戳

首页 > 解决方案 > Mysql 返回之前的行在当前日期和时间之前

mysql - Mysql 返回之前的行在当前日期和时间之前

问题描述

我有一个看起来像这样的 mysql 表：

itemNumber  mainDate      deliverTime
654         2019-12-16    7:30 AM
659         2019-12-16    9:30 PM
672         2019-12-17    10:30 AM
679         2019-12-17    5:00 PM

发布问题期间的当前时间是2019-12-17 1:13 PM

我想返回这段时间之前的所有行，所以我做了：

select itemNumber,mainDate,deliverTime from invTable
where mainDate < Current_Date()

这给了我今天之前的所有记录，其主要日期为2019-12-16

要返回今天的所有物品，但时间已经过去了，我试图这样做：

select itemNumber,mainDate,deliverTime from invTable
where mainDate < Current_Date() 
OR (mainDate = current_Date() and deliverTime <= current_time())

这给出了一个错误：

错误代码：1267。操作“<=”的排序规则（utf8_general_ci，IMPLICIT）和（latin1_swedish_ci，NUMERIC）的非法混合

标签： mysql

解决方案

错误消息似乎表明该deliverTime列不是TIME数据类型，但可能存储为VARCHAR. （这只是一个猜测；我不会猜测列的数据类型是什么。）

考虑一下：

SELECT t.itemnumber
     , t.maindate
     , t.delivertime
     , TIME(STR_TO_DATE(t.delivertime, '%h:%i %p')) AS tm
     , t.maindate + INTERVAL TIME_TO_SEC(TIME(STR_TO_DATE(t.delivertime,'%h:%i %p'))) SECOND AS deliver_datetime
     , DATE(NOW())
     , TIME(NOW())
 FROM ( SELECT '654' AS itemnumber, '2019-12-16' + INTERVAL 0 DAY AS maindate, '7:30 AM' AS delivertime 
        UNION ALL SELECT '659','2019-12-16','9:30 PM'
        UNION ALL SELECT '672','2019-12-17','10:30 AM'
        UNION ALL SELECT '679','2019-12-17','5:00 PM'
      ) t

请注意在从字符串转换为和数据类型的表达式中使用STR_TO_DATE, TIME,函数。TIME_TO_SECDATETIMETIME

要在 WHERE 子句中获得一个条件，该条件返回具有maindate和delivertimebefore组合的行NOW()，我们可以执行以下操作：

 WHERE t.maindate <= DATE(NOW())
   AND t.maindate + INTERVAL TIME_TO_SEC(TIME(STR_TO_DATE(t.delivertime,'%h:%i %p'))) SECOND 
       <= NOW()

或者我们可以这样做：

 WHERE t.maindate <= DATE(NOW())
   AND ( t.maindate < DATE(NOW()) OR TIME(STR_TO_DATE(v.delivertime, '%h:%i %p')) < TIME(NOW()) )

这只是几个可能的查询模式，使用单独的 DATE 和 TIME（存储为字符串）组件。

规范模式是将日期和时间组件存储到具有数据类型的单个列中DATETIME。

将它们一起存储到单个deliver_datetime列中，WHERE 子句中的条件将类似于：

 WHERE t.deliver_datetime <= NOW()

如果有特定的性能需要TIME单独存储部分，那么我们也可以这样做。

推荐阅读