SQL 将两个小时添加到时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30164819/
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
Add two hours to timestamp
提问by Matthias Brück
I've got a trigger that has to set a timestamp to the current time plus two hours when the field is null before insert. One of the statements I tried so far is
我有一个触发器，它必须将时间戳设置为当前时间加上在插入之前字段为空的两个小时。到目前为止我尝试过的陈述之一是
IF :new.time_to_live IS NULL THEN
:new.time_to_live := sysdate + INTERVAL '0 02:00:00.0' HOUR TO MINUTE;
END IF;
but I get a PLS-00166 Error (bad format for date, time, timestamp or interval literal) for the second row. Also modified it to several suggestions in multiple forums but the error stays. The column is created as follows:
但我收到第二行的 PLS-00166 错误（日期、时间、时间戳或间隔文字格式错误）。还在多个论坛中将其修改为几个建议，但错误仍然存在。该列的创建方式如下：
time_to_live timestamp(0) NOT NULL
回答by Alex Poole
You need to change your HOUR TO MINUTEto match the value you're actually passing:
您需要更改您的HOUR TO MINUTE以匹配您实际传递的值：
sysdate + INTERVAL '0 02:00:00.0' DAY TO SECOND
You might also want to use systimestampinstead of sysdate. You can use a shorter interval literal too if you're always adding exactly two hours:
您可能还想使用systimestamp代替sysdate. 如果您总是添加正好两个小时，您也可以使用更短的间隔文字：
systimestamp + INTERVAL '02:00' HOUR TO MINUTE
or just
要不就
systimestamp + INTERVAL '2' HOUR
As a quick demo:
作为一个快速演示：
SELECT systimestamp, systimestamp + INTERVAL '2' HOUR FROM DUAL;
SYSTIMESTAMP SYSTIMESTAMP+INTERVAL'2'HOUR
----------------------------------- -----------------------------------
11-MAY-15 11.15.22.235029000 +01:00 11-MAY-15 13.15.22.235029000 +01:00
回答by Gordon Linoff
Alther intervalcan really be convenient, I often write this as:
Altherinterval真的很方便，我经常这样写：
IF :new.time_to_live IS NULL THEN
:new.time_to_live := sysdate + 2 / 24.0
END IF;
Adding an integer to a date is treated as a number of days. "2 / 24" is two hours.
将整数添加到日期被视为天数。“2 / 24”是两个小时。
