如何将来自不同 MySQL 列的日期和时间组合起来与整个 DateTime 进行比较?

mysqlmysqli database更新于 2024/12/17 23:08:17

您可以借助 CONCAT() 函数将来自不同 MySQL 列的日期和时间组合起来与整个日期时间进行比较。语法如下 −

SELECT *FROM yourTableName WHERE CONCAT(yourDateColumnName,'',yourTimeColumnName) > 'yourDateTimeValue';

为了理解上述语法，让我们创建一个表。创建表的查询如下 −

mysql> create table DifferentDateTime
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ArrivalDate date,
    -> ArrivalTime time
    -> );
Query OK, 0 rows affected (1.53 sec)

使用 insert 命令在表中插入一些记录。查询语句如下 −

mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values(curdate(),'10:27:30');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2016-04-24','15:00:00');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2015-12-25','12:30:30');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2012-01-31','16:00:30');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2000-07-12','09:45:31');
Query OK, 1 row affected (0.11 sec)

示例

使用 select 语句显示表中的所有记录。查询如下 −

mysql> select *from DifferentDateTime;

输出

+----+-------------+-------------+
| Id | ArrivalDate | ArrivalTime |
+----+-------------+-------------+
|  1 |  2019-02-14 |    10:27:30 |
|  2 |  2016-04-24 |    15:00:00 |
|  3 |  2015-12-25 |    12:30:30 |
|  4 |  2012-01-31 |    16:00:30 |
|  5 |  2000-07-12 |    09:45:31 |
+----+-------------+-------------+
5 rows in set (0.00 sec)

示例

以下查询用于组合不同列中的日期和时间并与日期时间值进行比较 −

mysql> select *from DifferentDateTime where concat(ArrivalDate,'',ArrivalTime) > '2007-01-31 12:30:30';

输出

+----+-------------+-------------+
| Id | ArrivalDate | ArrivalTime |
+----+-------------+-------------+
|  1 |  2019-02-14 |    10:27:30 |
|  2 |  2016-04-24 |    15:00:00 |
|  3 |  2015-12-25 |    12:30:30 |
|  4 |  2012-01-31 |    16:00:30 |
+----+-------------+-------------+
4 rows in set (0.00 sec)