SQL:获取在特定日期范围内创建的记录 [英] SQL: Get records created in time range for specific dates

问题描述

我有一组上周创建的记录，我只想检索那些在6h45到19h15之间创建的记录.我有一列可以使用的creation_date.

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_date that I can use.

如何在sql中执行此操作?

How can I do this in sql?

推荐答案

在Oracle中，我们可以将日期转换为数字并以多种方式对其应用算术运算.

In Oracle we can turn dates into numbers and apply arithmetic to them in a variety of ways.

例如，sysdate-7给我们提供了7天前的日期. trunc(some_date)从日期列中删除时间元素. to_char(some_date, 'SSSSS')为我们提供其时间元素，即自午夜以来的秒数.所以06:45:00是24300秒，而18:15:59是69359秒(请检查这些数字，因为它们是信封后面的数字).

For instance sysdate-7 gives 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').

无论如何，将所有内容放在这样的单个查询中……

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.

这篇关于SQL:获取在特定日期范围内创建的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！