CODEWITHSUNDEEP
sqloracle-database
Travis
Travis

Reputation: 2245

Oracle Add 1 hour in SQL

I am just trying to add 1 hour to a value, it is kind of complicated on where and why i am doing this but basically i just need to query something like this 

select DATE_ADD(hh,1,'2014-10-15 03:30:00 pm') from dual

I keep reading old articles that say to use dateAdd or date_add but I keep getting invalid identifier errors.

Upvotes: 31

Views: 193445

Answers (6)

BaSsGaz
BaSsGaz

Reputation: 784

To add/subtract from a DATE, you have 2 options :

Method #1 : The easiest way is to use + and - to add/subtract days, hours, minutes, seconds, etc.. from a DATE, and ADD_MONTHS() function to add/subtract months and years from a DATE. Why ? That's because from days, you can get hours and any smaller unit (1 hour = 1/24 days), (1 minute = 1/1440 days), etc... But you cannot get months and years, as that depends on the month and year themselves, hence ADD_MONTHS() and no add_years(), because from months, you can get years (1 year = 12 months).

Let's try them :

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')             FROM dual;        -- prints current date:    19-OCT-2019 20:42:02 
SELECT TO_CHAR((SYSDATE + 1/24), 'DD-MON-YYYY HH24:MI:SS')    FROM dual;        -- prints date + 1 hour:   19-OCT-2019 21:42:02
SELECT TO_CHAR((SYSDATE + 1/1440), 'DD-MON-YYYY HH24:MI:SS')  FROM dual;        -- prints date + 1 minute: 19-OCT-2019 20:43:02 
SELECT TO_CHAR((SYSDATE + 1/86400), 'DD-MON-YYYY HH24:MI:SS') FROM dual;        -- prints date + 1 second: 19-OCT-2019 20:42:03 
-- Same goes for subtraction.

SELECT SYSDATE                  FROM dual;       -- prints current date:     19-OCT-19
SELECT ADD_MONTHS(SYSDATE, 1)   FROM dual;       -- prints date + 1 month:   19-NOV-19
SELECT ADD_MONTHS(SYSDATE, 12)  FROM dual;       -- prints date + 1 year:    19-OCT-20
SELECT ADD_MONTHS(SYSDATE, -3)  FROM dual;       -- prints date - 3 months:  19-JUL-19

Method #2 : Using INTERVALs, you can or subtract an interval (duration) from a date easily. More than that, you can combine to add or subtract multiple units at once (e.g 5 hours and 6 minutes, etc..) Examples :

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')                                        FROM dual;        -- prints current date:                 19-OCT-2019 21:34:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' HOUR), 'DD-MON-YYYY HH24:MI:SS')                  FROM dual;        -- prints date + 1 hour:                19-OCT-2019 22:34:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' MINUTE), 'DD-MON-YYYY HH24:MI:SS')                FROM dual;        -- prints date + 1 minute:              19-OCT-2019 21:35:15
SELECT TO_CHAR((SYSDATE + INTERVAL '1' SECOND), 'DD-MON-YYYY HH24:MI:SS')                FROM dual;        -- prints date + 1 second:              19-OCT-2019 21:34:16
SELECT TO_CHAR((SYSDATE + INTERVAL '01:05:00' HOUR TO SECOND), 'DD-MON-YYYY HH24:MI:SS') FROM dual;        -- prints date + 1 hour and 5 minutes:  19-OCT-2019 22:39:15
SELECT TO_CHAR((SYSDATE + INTERVAL '3 01' DAY TO HOUR), 'DD-MON-YYYY HH24:MI:SS')        FROM dual;        -- prints date + 3 days and 1 hour:     22-OCT-2019 22:34:15
SELECT TO_CHAR((SYSDATE - INTERVAL '10-3' YEAR TO MONTH), 'DD-MON-YYYY HH24:MI:SS')      FROM dual;        -- prints date - 10 years and 3 months: 19-JUL-2009 21:34:15

Upvotes: 4

Bhargav Avancha
Bhargav Avancha

Reputation: 71

Old way:

SELECT DATE_COLUMN + 1 is adding a day
SELECT DATE_COLUMN + N /24 to add hour(s) - N being number of hours
SELECT DATE_COLUMN + N /1440 to add minute(s) - N being number of minutes
SELECT DATE_COLUMN + N /86400 to add second(s) - N being number of seconds

Using INTERVAL:

SELECT DATE_COLUMN + INTERVAL 'N' HOUR or MINUTE or SECOND - N being a number of hours or minutes or seconds.

Upvotes: 7

Himanshu sharma
Himanshu sharma

Reputation: 7891

The calculation is simple

if you want to add 1 hour in the date .

every day have 24 hour , you can add .

select sysdate + 1/24 from dual;

if you want 1 day to add 

select sysdate + 24/24 from dual; 

or 

select sysdate + 1 from dual; 

same as for 2, 3 , 4 day

For static date you have the answer below.

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 5565

You can use INTERVAL type or just add calculated number value - "1" is equal "1 day".

first way:

select date_column + INTERVAL '0 01:00:00' DAY TO SECOND from dual;

second way:

select date_column + 1/24 from dual;

First way is more convenient when you need to add a complicated value - for example, "1 day 3 hours 25 minutes 49 seconds". See also: http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php

Also you have to remember that oracle have two interval types - DAY TO SECOND and YEAR TO MONTH. As for me, one interval type would be better, but I hope people in oracle knows, what they do ;)

Upvotes: 7

user330315
user330315

Reputation:

Use an interval:

select some_date_column + interval '1' hour 
from your_table;

Upvotes: 28

Multisync
Multisync

Reputation: 8787

select sysdate + 1/24 from dual;

sysdate is a function without arguments which returns DATE type
+ 1/24 adds 1 hour to a date

select to_char(to_date('2014-10-15 03:30:00 pm', 'YYYY-MM-DD HH:MI:SS pm') + 1/24, 'YYYY-MM-DD HH:MI:SS pm') from dual;

Upvotes: 71

Related Questions