SQL 如何使用SQL日期时间字段在特定时间后获取记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/176673/
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 get records after a certain time using SQL datetime field
提问by thursdaysgeek
If I have a datetime field, how do I get just records created later than a certain time, ignoring the date altogether?
如果我有一个日期时间字段，我如何只在某个时间之后创建记录，而完全忽略日期？
It's a logging table, it tells when people are connecting and doing something in our application. I want to find out how often people are on later than 5pm.
它是一个日志表，它告诉人们何时在我们的应用程序中连接和执行某些操作。我想知道人们在下午 5 点以后上班的频率。
(Sorry - it is SQL Server. But this could be useful for other people for other databases)
（对不起 - 它是 SQL Server。但这对于其他数据库的其他人可能有用）
回答by Luke Bennett
For SQL Server:
对于 SQL Server：
select * from myTable where datepart(hh, myDateField) > 17
See http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx.
请参阅http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx。
回答by Thilo
What database system are you using? Date/time functions vary widely.
你用的是什么数据库系统？日期/时间函数差别很大。
For Oracle, you could say
对于 Oracle，你可以说
SELECT * FROM TABLE
WHERE TO_CHAR(THE_DATE, 'HH24:MI:SS') BETWEEN '17:00:00' AND '23:59:59';
Also, you probably need to roll-over into the next day and also select times between midnight and, say, 6am.
此外，您可能需要滚动到第二天，并选择午夜和早上 6 点之间的时间。
回答by ysth
In MySQL, this would be
在 MySQL 中，这将是
where time(datetimefield) > '17:00:00'
回答by Zaxxon
For MSSQL use the CONVERT method:
对于 MSSQL，使用 CONVERT 方法：
DECLARE @TempDate datetime = '1/2/2016 6:28:03 AM'
SELECT
@TempDate as PassedInDate,
CASE
WHEN CONVERT(nvarchar(30), @TempDate, 108) < '06:30:00' then 'Before 6:30am'
ELSE 'On or after 6:30am'
END,
CASE
WHEN CONVERT(nvarchar(30), @TempDate, 108) >= '10:30:00' then 'On or after 10:30am'
ELSE 'Before 10:30am'
END
回答by Marc Gravell
The best thing I can think would be: don't use a DateTime field; well, you could use a lot of DATEADD/DATEPART etc, but it will be slow if you have a lot of data, as it can't really use an index here. Your DB may offer a suitable type natively - such as the TIME type in SQL Server 2008 - but you could just as easily store the time offset in minutes (for example).
我能想到的最好的事情是：不要使用 DateTime 字段；好吧，你可以使用很多 DATEADD/DATEPART 等，但是如果你有很多数据，它会很慢，因为它不能在这里真正使用索引。您的数据库可能本身就提供了合适的类型 - 例如 SQL Server 2008 中的 TIME 类型 - 但您也可以轻松地以分钟为单位存储时间偏移量（例如）。
回答by thursdaysgeek
Ok, I've got it.
好的，我知道了。
select myfield1,
myfield2,
mydatefield
from mytable
where datename(hour, mydatefield) > 17
This will get me records with a mydatefield with a time later than 5pm.
这将使我的时间晚于下午 5 点的 mydatefield 记录。
回答by David Aldridge
Another Oracle method for simple situations:
用于简单情况的另一种 Oracle 方法：
select ...
from ...
where EXTRACT(HOUR FROM my_date) >= 17
/
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions050.htm#SQLRF00639
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions050.htm#SQLRF00639
Tricky for some questions though, like all records with the time between 15:03:21 and 15:25:45. I'd also use the TO_CHAR method there.
但有些问题很棘手，比如所有时间在 15:03:21 和 15:25:45 之间的记录。我也会在那里使用 TO_CHAR 方法。
回答by Jonathan Leffler
In Informix, assuming that you use a DATETIME YEAR TO SECOND field to hold the full date, you'd write:
在 Informix 中，假设您使用 DATETIME YEAR TO SECOND 字段来保存完整日期，您可以这样写：
WHERE EXTEND(dt_column, HOUR TO SECOND) > DATETIME(17:00:00) HOUR TO SECOND
'EXTEND' can indeed contract the set of fields (as well as extend it, as the name suggests).
'EXTEND' 确实可以收缩字段集（以及扩展它，顾名思义）。
As Thilo noted, this is an area of extreme variability between DBMS (and Informix is certainly one of the variant ones).
正如 Thilo 所指出的，这是 DBMS 之间存在极大差异的领域（而 Informix 肯定是其中的一个变体）。
