如何组合来自不同 MySQL 列的日期和时间以与完整的 DateTime 进行比较？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9187437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to combine date and time from different MySQL columns to compare to a full DateTime?
提问by Ivan
Column dis DATE, column tis time, column vis, for example, INT. Let's say I need all the values recorded after 15:00 of 01 Feb 2012 and on. If I write
列d是 DATE，列t是时间，v例如，列是 INT。假设我需要在 2012 年 2 月 1 日 15:00 之后记录的所有值。如果我写
SELECT * FROM `mytable` WHERE `d` > '2012-02-01' AND `t` > '15:00'
all the records made before 15:00 at any date are going to be excluded from the result set (as well as all made at 2012-02-01) while I want to see them. It seems it would be easy if there were a single DATETIME column, but there are separate columns for date and time instead in the case of mine.
在任何日期 15:00 之前制作的所有记录都将从结果集中排除（以及所有在 2012-02-01 制作的记录），而我想查看它们。如果只有一个 DATETIME 列似乎很容易，但在我的情况下，日期和时间有单独的列。
The best I can see now is something like
我现在能看到的最好的是
SELECT * FROM `mytable` WHERE `d` >= '2012-02-02' OR (`d` = '2012-02-01' AND `t` > '15:00')
Any better ideas? Maybe there is a function for this in MySQL? Isn't there something like
有什么更好的想法吗？也许在 MySQL 中有一个函数？是不是有类似的东西
SELECT * FROM `mytable` WHERE DateTime(`d`, `t`) > '2012-02-01 15:00'
possible?
可能的？
回答by Brian Glaz
You can use the mysql CONCAT()function to add the two columns together into one, and then compare them like this:
您可以使用mysqlCONCAT()函数将两列加在一起，然后像这样比较它们：
SELECT * FROM `mytable` WHERE CONCAT(`d`,' ',`t`) > '2012-02-01 15:00'
回答by Brad Mace
The TIMESTAMP(expr1,expr2)function is explicitly for combining date and time values:
该TIMESTAMP(expr1,expr2)函数明确用于组合日期和时间值：
With a single argument, this function returns the date or datetime expression
expras a datetime value. With two arguments, it adds the time expressionexpr2to the date or datetime expressionexpr1and returns the result as a datetime value.
使用单个参数，此函数将日期或日期时间表达式
expr作为日期时间值返回。使用两个参数，它将时间表达式添加expr2到日期或日期时间表达式expr1，并将结果作为日期时间值返回。
This resulting usage is just what you predicted:
由此产生的用法正是您所预测的：
SELECT * FROM `mytable` WHERE TIMESTAMP(`d`, `t`) > '2012-02-01 15:00'
回答by cmc
Here's a clean version that doesn't require string operations or conversion to to UTC timestamps across time zones.
这是一个干净的版本，不需要字符串操作或跨时区转换为 UTC 时间戳。
DATE_ADD(date, INTERVAL time HOUR_SECOND)
回答by Krishna Deepak
All you have to do is to convert it into unix timestamp and make appropriate selections. For this you have to use mysql functions like *unix_timestamp().* and *date_format*
您所要做的就是将其转换为 unix 时间戳并进行适当的选择。为此，您必须使用 mysql 函数，如 *unix_timestamp().* 和 *date_format*
Suppose you want to select rows where timestamp > 1328725800, the following sql statement would do the task.
假设您要选择时间戳 > 1328725800 的行，以下 sql 语句将完成该任务。
select unix_timestamp(d)+3600*date_format(t,'%h)+60*date_format(t,'%i')+date_format(t,'%S') as timestamp from table where timestamp>1328725800
回答by 1''
Actually it should be:
其实应该是：
SELECT * FROM `mytable` WHERE CONCAT(`d`,' ',`t`) > '2012-02-01 15:00:00'
If you want to take seconds into account, you need to add the two digits to the end ;)
如果要考虑秒数，则需要在末尾添加两位数字；)
