如何在Oracle中从当前时间返回24小时

Question

我的Oracle数据库中有一列，它以以下格式记录用户的创建时间30-NOV-20 11.49.11.000000000 AM(TIMESTAMP(6(格式(。我想做的是选择创建时间比当前时间早24小时的所有记录所以我要做的是从当前时间中减去1并进行比较。但当我减去1时，它只返回日期。

select * from user where created_date < SYSTIMESTAMP-1

dbms_output.put_line (SYSTIMESTAMP-1);->29-NOV-20

缺少时间部分，这使我无法与表中创建的时间进行比较请帮我完成这项任务。

Answer 1

如果从date数据类型(例如sysdate(中减去1，总有一天它会将您移回。但是，如果从timestamp数据类型值中减去它，Oracle将把它转换为date并返回一个日期(此外，它还会被截断(。

参见以下示例：

SQL> select
2    systimestamp val1,
3    systimestamp - 1 val2,
4    --
5    systimestamp - interval '1' day val3
6  from dual;
VAL1
-----------------------------------------------------
VAL2
--------
VAL3
-----------------------------------------------------
30.11.20 09:55:01,439352 +01:00
29.11.20
29.11.20 09:55:01,439352000 +01:00

SQL>

因此，您应该做的是减去间隔，即

select *
from user
where created_date < systimestamp - interval '1' day;

Answer 2

一半的问题是，用于显示值的任何客户端程序都使用其区域的默认日期格式，并且该默认格式设置为DD-MON-RR。

您可以更改NLS_DATE_FORMAT和NLS_TIMESTAMP_FORMAT会话参数，这将(假设您的客户端程序使用它们而不是一些内部设置(为您提供您期望的输出：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9TZR';

然后

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

输出(取决于您的系统时区(：

SYSDATE|SYSTIMESTAMP：------------------|：----------------------------------2020-11-30 10:12:22|2020-11-3010:12:22.476282000+00:0

如果使用SYSTIMESTAMP-1，则Oracle不支持从TIMESTAMP [WITH TIME ZONE]数据类型中减去NUMBER数据类型，但它确实支持从DATE数据类型中减少NUMBER数据类型，并将执行从TIMESTAMP到DATE的隐式强制转换，以便查询有效。

例如：

SELECT SYSDATE - 1, SYSTIMESTAMP - 1, SYSTIMESTAMP - INTERVAL '1' DAY FROM DUAL;

输出：

SYSDATE-1|SYSTIMESTAMP-1|SYSTIMESTAMP-INTERVAL'1'天：------------------|：------------------|----------------------------------2020-11-29 10:24:02|2020-11-29-10:24:02| 2020-11-29:10:24:02651735000+00:00

您可以看到，在中间一列SYSTIMESTAMP-1给出与SYSDATE-1相同的输出，但在右侧一列中，减去一个间隔可以确保维持TIMESTAMP WITH TIME ZONE数据类型。

所以你的查询：

SELECT * FROM user WHERE created_date <  SYSTIMESTAMP-1

有效：

SELECT * FROM user WHERE created_date <  CAST( SYSTIMESTAMP AS DATE )-1

它将具有完全相同的年、月、日、小时、分钟和(整数(秒分量，但将丢失SYSTIMESTAMP中的小数秒和时区信息。

如果您的列没有时区数据，并且小数秒的精度水平对您来说无关紧要，那么您的查询将能够正常工作。

但是，如果你想保留时区和/或分数秒信息，那么你可以使用：

SELECT * FROM user WHERE created_date <  SYSTIMESTAMP - INTERVAL '1' DAY;

但是，如果created_date是DATE列，则可能需要：

SELECT * FROM user WHERE created_date <  SYSDATE - INTERVAL '1' DAY;

或

SELECT * FROM user WHERE created_date <  SYSDATE - 1;

db<gt；小提琴这里

Answer 3

您可以将其用作(SYSDATE，-1(。此外，如果你想在24小时内搜索新记录，它应该是"；created_date>=(sysdate-1("；。