Adding days to TIMESTAMP WITH TIMEZONE column

Database

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.

1 REPLY
Teradata Employee

Re: Adding days to TIMESTAMP WITH TIMEZONE column

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)