SQL：获取在特定日期的时间范围内创建的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9568787/
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
SQL: Get records created in time range for specific dates
提问by Mouna Cheikhna
I have a set of records that were created last week, and from those I want to retrieve only those that were created between 6h45 and 19h15. I have a column creation_datethat I can use.
我有一组上周创建的记录，我只想检索那些在 6h45 和 19h15 之间创建的记录。我有一个creation_date可以使用的专栏。
How can I do this in sql?
我怎样才能在 sql 中做到这一点？
回答by APC
In Oracle we can turn dates into numbers and apply arithmetic to them in a variety of ways.
在 Oracle 中，我们可以将日期转换为数字，并以多种方式对它们应用算术运算。
For instance sysdate-7gives us the date seven days ago. trunc(some_date)removes the time element from a date column. And to_char(some_date, 'SSSSS')gives us its time element as the number of seconds since midnight. So 06:45:00 is 24300 seconds and 18:15:59 is 69359 seconds (please check those numbers, as they are back-of-an-envelope figgerin').
例如sysdate-7给我们 7 天前的日期。 trunc(some_date)从日期列中删除时间元素。并将to_char(some_date, 'SSSSS')其时间元素作为自午夜以来的秒数。所以 06:45:00 是 24300 秒，18:15:59 是 69359 秒（请检查这些数字，因为它们是信封背面的数字）。
Anyway, putting that all together in a single query like this ...
无论如何，将所有这些放在一个像这样的查询中......
select *
from your_table
where creation_date >= trunc(sysdate)-7
and to_number(to_char(creation_date, 'sssss')) between 24300 and 69359
... wil produce all the records created in the last week with a time element within core hours.
... 将生成上周创建的所有记录，并在核心小时内使用时间元素。
回答by Doug Porter
This query would return any records created in the last 7 days with the time portion of their create date between 6:45am and 7:15pm.
此查询将返回在过去 7 天内创建的所有记录，其创建日期的时间部分在上午 6:45 到晚上 7:15 之间。
select *
from your_table
where creation_date > sysdate - 7
and to_char(creation_date, 'hh24:mi:ss') >= '06:45:00'
and to_char(creation_date, 'hh24:mi:ss') <= '19:15:00'
回答by Zack Macomber
Based on criteria above, this will bring back records from the last week between Sunday and Saturday in the hours of 06:45:00 and 19:15:59.
根据上述标准，这将带回上周周日和周六 06:45:00 和 19:15:59 之间的记录。
You can adjust the " - 7" and the " + 6" to different numbers if you'd like the week range to be different (for instance, if your week is Monday through Sunday).
如果您希望周范围不同（例如，如果您的一周是周一到周日），您可以将“- 7”和“+ 6”调整为不同的数字。
select *
from your_table
where creation_date >= TRUNC(SYSDATE, 'WW') - 7
and creation_date <= (TRUNC(SYSDATE, 'WW') - 7) + 6
and to_char(creation_date, 'hh24:mi:ss') >= '06:45:00'
and to_char(creation_date, 'hh24:mi:ss') <= '19:15:00'
