Mysql 比较两个日期时间字段
我想比较两个日期和时间,我想要来自 tbl 的所有结果 其中 date1 > date2
Select * From temp where mydate > '2009-06-29 04:00:44';
但它只是比较日期而不是时间。 它给了我今天日期的所有结果集
'2009-06-29 11:08:57'
'2009-06-29 11:14:35'
'2009-06-29 11:12:38'
'2009-06-29 11:37:48'
'2009-06-29 11:52:17'
'2009-06-29 12:12:50'
'2009-06-29 12:13:38'
'2009-06-29 12:19:24'
'2009-06-29 12:27:25'
'2009-06-29 12:28:49'
'2009-06-29 12:35:54'
'2009-06-29 12:36:54'
'2009-06-29 12:49:57'
'2009-06-29 12:58:04'
'2009-06-29 04:13:20'
'2009-06-29 04:56:19'
'2009-06-29 05:00:23'
'2009-06-29 05:04:26'
'2009-06-29 05:08:17'
'2009-06-29 05:26:57'
'2009-06-29 05:29:06'
'2009-06-29 05:32:11'
'2009-06-29 05:52:07'
I want to compare two dates with time, I want all the results from tbl where date1 > date2
Select * From temp where mydate > '2009-06-29 04:00:44';
but it is just comparing dates not time. it is giving me all the result set of today's date
'2009-06-29 11:08:57'
'2009-06-29 11:14:35'
'2009-06-29 11:12:38'
'2009-06-29 11:37:48'
'2009-06-29 11:52:17'
'2009-06-29 12:12:50'
'2009-06-29 12:13:38'
'2009-06-29 12:19:24'
'2009-06-29 12:27:25'
'2009-06-29 12:28:49'
'2009-06-29 12:35:54'
'2009-06-29 12:36:54'
'2009-06-29 12:49:57'
'2009-06-29 12:58:04'
'2009-06-29 04:13:20'
'2009-06-29 04:56:19'
'2009-06-29 05:00:23'
'2009-06-29 05:04:26'
'2009-06-29 05:08:17'
'2009-06-29 05:26:57'
'2009-06-29 05:29:06'
'2009-06-29 05:32:11'
'2009-06-29 05:52:07'
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
您想要作为示例显示的查询是:
04:00:00 是凌晨 4 点,因此您显示的所有结果都出现在该查询之后,这是正确的。
如果您想在下午 4 点之后显示所有内容,则需要在查询中使用正确的(24 小时)表示法。
为了让事情变得更清楚一点,试试这个:
这将显示日期和 12 小时时间。
The query you want to show as an example is:
04:00:00 is 4AM, so all the results you're displaying come after that, which is correct.
If you want to show everything after 4PM, you need to use the correct (24hr) notation in your query.
To make things a bit clearer, try this:
That will show you the date, and its 12hr time.
您可以使用以下 SQL 来比较日期和时间 -
当我在问题中提到的相同类型的表和字段上使用相同的 SQL 时附加的 mysql 输出 -
它应该可以完美工作。
You can use the following SQL to compare both date and time -
Attached mysql output when I used same SQL on same kind of table and field that you mentioned in the problem-
It should work perfect.
您的查询显然返回了所有正确的日期,即使考虑到时间也是如此。
如果您对结果仍然不满意,请尝试 DATEDIFF 并查找两个日期之间的负/正结果。
确保您的
mydate
列是datetime
类型。Your query apparently returned all correct dates, even considering the time.
If you're still not happy with the results, give DATEDIFF a shot and look for negaive/positive results between the two dates.
Make sure your
mydate
column is adatetime
type.您想订购吗?
Do you want to order it?