ORACLE SQL: add time to a timestamp with timezone

Question

I need to add seconds and substract variables with type TIMESTAMP WITH TIMEZONE, however, to my understanding, adding numbers to such data causes the information about the timezone to be lost, perhaps because it's converted to a DATE type

That is:

  SELECT FROM_TZ(
           TO_TIMESTAMP( 
             TO_DATE('03/09/2012 2:30:30','DD/MM/YYYY HH:MI:SS')
           )
         , 'America/Chicago') 
      FROM DUAL;

Gives:

 03/09/2012 00:00:00, -05:00

Then

  SELECT FROM_TZ(
           TO_TIMESTAMP( 
             TO_DATE('03/09/2012 2:30:30','DD/MM/YYYY HH:MI:SS')
           )
         , 'America/Chicago')  + 1/24 -- add 1 hour
      FROM DUAL;

Gives

 03/09/2012 01:00:00

and loses the timezone information. But

 SELECT FROM_TZ(
     TO_TIMESTAMP( 
       TO_DATE('03/09/2012 2:30:30','DD/MM/YYYY HH:MI:SS'))
      , 'America/Chicago') +  INTERVAL '1' hour

  FROM DUAL;

Correctly gives

 03/09/2012 01:00:00,000000000 -05:00

However the INTERVAL.. syntax expect a char constant, so I can't use that with variables.

How can I perform that kind of arithmetic with TIMESTAMP WITH TIME ZONE datatype while retaining timezone information?

TIA

Brian · Accepted Answer · 2012-09-17 23:04:26Z

3

You can use the NUMTODSINTERVAL function to convert a number to an interval.

SELECT FROM_TZ( TIMESTAMP '2012-10-08 00:00:00','-5:00') 
  + NUMTODSINTERVAL(1,'HOUR')
FROM dual;

answered Sep 17, 2012 at 23:04

Brian

7,4402 gold badges30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ORACLE SQL: add time to a timestamp with timezone

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related