CODEWITHSUNDEEP
sqloracle-databasetimestamp-with-timezone
user881430
user881430

Reputation: 205

ORACLE SQL: add time to a timestamp with timezone

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

Upvotes: 2

Views: 10344

Answers (1)

Brian
Brian

Reputation: 7289

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;

Upvotes: 3

Related Questions