如何在 SQL 中找到两个特定时间之间的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7079543/
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 can I find the data between two specific times in SQL
提问by Withdalot
I need to run a query that finds all login dates (dd/mm/yyyy) and times (hh.mm) that occurred between 2am yesterday and 2am today. The login time in the database is formatted as mm-dd-yyyy hh.mm.ss.
我需要运行一个查询来查找昨天凌晨 2 点到今天凌晨 2 点之间发生的所有登录日期 ( dd/mm/yyyy) 和时间 ( hh.mm)。数据库中的登录时间格式为mm-dd-yyyy hh.mm.ss.
I have tried multiple selectand betweenqueries that either return data on the wrong day or outside the time spans.
我曾尝试多个select和between的查询在错误的一天或时间跨度之外的任何返回数据。
回答by James Hill
Based on the information that you provided, I can only give a generic example:
根据你提供的信息，我只能举一个通用的例子：
SELECT *
FROM [YourTable]
WHERE [YourDate] BETWEEN '08-15-2011 02:00:00' AND '08-16-2011 02:00:00'
**EDIT**
**编辑**
Per a good suggestion from the comments, here is a reusable version:
根据评论中的一个很好的建议，这是一个可重复使用的版本：
SELECT *
FROM [YourTable]
WHERE [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'
AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'
回答by WhatAWorld
Assuming mysql:
假设mysql：
SELECT * FROM your_table
WHERE STR_TO_DATE(your_date, '%m-%d-%Y %H.%i.%s') BETWEEN
DATE_SUB(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 22 HOUR) AND
DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 2 HOUR)
I'm sure there's a better way though.
不过我相信有更好的方法。
回答by venkat
The query doesn't return any records if we use as follows...
如果我们使用如下，查询不会返回任何记录......
SELECT *
FROM [YourTable]
WHERE [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'
AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'
We are using between clause so the oldest date should be first and the query becomes as follows...
我们正在使用 between 子句，所以最旧的日期应该是第一个，查询变成如下...
SELECT *
FROM [YourTable]
WHERE [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'
AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'
回答by Piyush Ssharma
This can also help you out
这也可以帮助你
where pt.status in (1)
and pt.is_visible in ('t')
and pt.merchant in (0)
and (date(pt.created_at+'05:30') >= current_date-2
and extract(hour from(pt.created_at+'05:30')) >=17)
and ((pt.created_at+'05:30') <= current_date-1
and extract(hour from(pt.created_at+'05:30')) <=17)
回答by vidu
I think you can use these tips :
我想你可以使用这些技巧：
SET @start = CURDATE() - interval 1 DAY + interval 2 HOUR;
SET @end = CURDATE() + interval 2 HOUR;
And :
和 ：
SELECT * FROM TABLE_NAME WHERE DATE_FIELD BETWEEN @start AND @end
