在我的输入表中,我试图将小时数(NUMERIC)添加到时间戳中
time_of_Day
2020-10-01 22:15:00
2020-11-01 15:04:00
hours_to_add
3
4
Expected result
2020-10-02 01:15:00
2020-11-01 19:04:00
我尝试了以下方法,但出现错误:类型间隔的输入语法无效:“ hours_to_add”
SELECT
time_of_Day+ interval 'hours_to_add' hour
from your_table;
您可以这样做:
select time_of_Day + hours_to_add/24 from your_table;
这将产生date
数据类型的结果。
如果需要间隔算术(并具有timestamp
数据类型的结果):
select time_of_day + hours_to_add * interval '1' hour from your_table
with t as (
select timestamp '2020-10-01 22:15:00' time_of_Day , 3 hours_to_add from dual
union all select timestamp '2020-11-01 15:04:00', 4 from dual
)
select
t.*,
time_of_day + hours_to_add/24 as_date,
time_of_day + hours_to_add * interval '1' hour as_timestamp
from t
TIME_OF_DAY | HOURS_TO_ADD | AS_DATE | AS_TIMESTAMP :------------------ | -----------:| | :------------------ | :------------------ 2020-10-01 22:15:00 | 3 | 2020-10-02 01:15:00 | 2020-10-02 01:15:00 2020-11-01 15:04:00 | 4 | 2020-11-01 19:04:00 | 2020-11-01 19:04:00
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句