All Forums Database
vidyadutt.s 6 posts Joined 09/13
13 Mar 2014
Adding days to TIMESTAMP WITH TIMEZONE column

Hello Experts,
I need to add some days to a timestamp with timezone value. Both values come from a table. Is it possible to use INTERVAL here? I tried the following, but it's throwing error.

SELECT  Start_Date
       ,Count_Days
       ,Start_Date + INTERVAL Count_Days AS End_Date
FROM(
SELECT  (CURRENT_TIMESTAMP - INTERVAL '18' DAY) AS START_DATE
       ,CAST(10 AS SMALLINT) AS COUNT_DAYS
)A

Then I extracted date, added the days, then concatenated it with the rest of timestamp from Start_Date. However, I'm not able to convert it back to Timestamp with Timezone. When I try, it's truncating the timezone part (+00:00).

SELECT  Start_Date
       ,Count_Days
       ,CAST(CAST((CAST((CAST(Start_Date AS DATE) + Count_Days) AS CHAR(10)) || ' ' || SUBSTRING(CAST(Start_Date AS CHAR(32)) FROM 12)) AS CHAR(32)) AS TIMESTAMP FORMAT 'YYYY-MM-DDbHH:MI:SS.S(F)Z') AS End_Date
FROM(
SELECT  (CURRENT_TIMESTAMP - INTERVAL '18' DAY) AS START_DATE
       ,CAST(10 AS SMALLINT) AS COUNT_DAYS
)A

I'm looking for you experts to help me out here.

-Vidyadutt

Fred 1096 posts Joined 08/04
13 Mar 2014

In the first case, just use Start_Date + Count_Days * INTERVAL '1' DAY.
 
In the second, you need to say you want the time zone, i.e. CAST(... AS TIMESTAMP WITH TIME ZONE)

You must sign in to leave a comment.