CODEWITHSUNDEEP
sqloracle-databasedateselecttimestamp
jackie21
jackie21

Reputation: 337

Timestamp + Numeric hours in Oracle SQL

In my Input tables I am trying to add hours (NUMERIC) to a timestamp

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

I tried the following method but I get an error: invalid input syntax for type interval: "hours_to_add"

SELECT 
time_of_Day+ interval 'hours_to_add' hour 
from your_table;

Upvotes: 1

Views: 147

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.

So, if you don't want to display the fractional seconds, then use proper format mask using TO_CHAR to display what you want.

Here is a working demo:

with your_table as
(
  select timestamp '2020-10-01 22:15:00' time_of_Day , 3 hrs from dual
  union all 
  select timestamp '2020-11-01 15:04:00', 4 from dual
)
select time_of_day, 
       to_char(time_of_day + interval '1' hour * hrs, 'YYYY-MM-DD HH24:MI:SS') new_time 
from your_table;

TIME_OF_DAY                    NEW_TIME           
------------------------------ -------------------
01-10-20 10:15:00.000000000 PM 2020-10-02 01:15:00
01-11-20 3:04:00.000000000 PM  2020-11-01 19:04:00

The timestamp will still have the fractional seconds stored as 02-10-20 1:15:00.000000000 AM.

Upvotes: 1

user330315
user330315

Reputation:

Multiple the value with a fixed interval:

select time_of_day + interval '1' hour * hours_to_add
from the_table;

Online demo

Upvotes: 1

GMB
GMB

Reputation: 222412

You can just do:

select time_of_Day + hours_to_add/24 from your_table;

This produces a result of date datatype.

If you wanted interval arithmetics (and have a result of timestamp datatype):

select time_of_day + hours_to_add * interval '1' hour from your_table

Demo on DB Fiddle:

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

Upvotes: 2

Related Questions