Add Day to Timestamp

Question

How do I add days to a timestamp? If my timestamp is 01-JAN-2011 11-09-05 and I add 2 days, I want 03-JAN-2011 11-09-05.

score 32 · Answer 1 · answered May 26 '11 at 17:53

32

select '01-jan-2011 11-09-05' + interval '2' day

answered May 26 '11 at 17:53

Marc B

7

+1. A more complete Oracle example: SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + INTERVAL '2' DAY FROM dual; – DCookie May 26 '11 at 18:08
Using interval seems better than using `+` because I noticed loss of timezone with the latter. – Antoine Martin Nov 20 '19 at 11:26

score 4 · Answer 2 · answered May 26 '11 at 18:36

4

A completely Oracle-centric solution is to simply add 2 to the timestamp value as the default interval is days for Oracle dates/timestamps:

SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + 2
  FROM dual;

answered May 26 '11 at 18:36

DCookie

1

a timestamp plus a numeric value yields a date. – llayland Dec 24 '11 at 04:04
you may lose the timezone by using `+`. – Antoine Martin Nov 20 '19 at 13:03

score 0 · Answer 3 · answered Feb 17 '22 at 12:55

0

In a similar case, I used:

SELECT TO_TIMESTAMP('01-jan-2011 11-09-05','DD-Mon-YYYY HH24-MI-SS') + NUMTODSINTERVAL(2, 'DAY')

Because, othewise, the expression is converted to DATE and precission is lost. See: NUMTODSINTERVAL documentation

answered Feb 17 '22 at 12:55

adcelis

3 Answers3