Adding a interval to datetimestamp from another column

Database

Adding a interval to datetimestamp from another column

hi All
is it possible to add a numberic column to a datatime column?
eg:
column 1:begin_date
column 2:add_hours

column 3 = begin_date + interval 'add_hours' hour

how do you do this in teradata?

thanks

10 REPLIES
Enthusiast

Re: Adding a interval to datetimestamp from another column

(BEGIN_DATE (TIMESTAMP(0))) + ( ADD_HOURS(INTERVAL HOUR) )
Enthusiast

Re: Adding a interval to datetimestamp from another column

Can this same thing be done with seconds? I have this problem:

TABLE
Start_timestamp TIMESTAMP
,Duration_time FLOAT

I want to do this:

select
start_timestamp
,start_timestamp + duration_time
from table

I've tried

select
start_timestamp
,start_timestamp + interval duration_time seconds
from table

and

select
start_timestamp
,(start_timestamp(timestamp(0))) +( duration_time(Interval second))
from table

and both get errors.
Enthusiast

Re: Adding a interval to datetimestamp from another column

Try this

select
start_timestamp
,start_timestamp + CAST(CAST(duration_time AS DEC(10,6)) AS interval second(4,6))
from table

make sure that FLOAT value should be in range +-9999.999999
Enthusiast

Re: Adding a interval to datetimestamp from another column

That one worked perfectly. Thanks for the help!
Enthusiast

Re: Adding a interval to datetimestamp from another column

I spoke a bit too quickly.
It worked at first, but then I ran into an overflow problem. Also, I was wrong in saying the duration field was a float, it's actually an integer.

but, I have values greater than 4 digits in that field so I get an overflow problem. Is "Interval Second(4,6)" as big as that field can get? Can I not have more than 9999.999999 seconds?

If not, I'd imagine I'd have to split up the number of seconds into a time field and then use that, something like

cast(trim(cast(cast(duration_time as decimal(18,6))/60/60 as integer)) || ':' || trim(cast(cast(duration_time as decimal(18,6))/60 as integer)) || ':' || trim(cast(substring ( cast(duration_time as decimal(18,6))/60 from position('.' in cast(duration_time as decimal(18,6))/60) for 3) * 60 as integer)) as time(0)) as Total_Duration

Can I add a time field to a timestamp field?
Enthusiast

Re: Adding a interval to datetimestamp from another column

I decided to go about this a bit differently and it worked.

Instead of the big long concatenation string, I decided on this statement:

SELECT
,start_timestamp
,cast(START_TIMESTAMP + cast(cast((DURATION_TIME/60) as dec(10,6)) as interval minute(4)) as timestamp(0)) + cast(trim(cast(substring ( cast(DURATION_TIME as decimal(18,6))/60 from position('.' in cast(DURATION_TIME as decimal(18,6))/60) for 3) * 60 as integer)) as interval second(4,6))
FROM
TABLE

This one works fine, even if long.
Senior Apprentice

Re: Adding a interval to datetimestamp from another column

Hi keno,
if the duration is less than 864,000,000 seconds:

start_timestamp +
(duration * INTERVAL '0000 00:00:01.000000' DAY TO SECOND)

Dieter
Enthusiast

Re: Adding a interval to datetimestamp from another column

I knew there had to be an easier option! That one works and makes cleaner code!
N/A

Re: Adding a interval to datetimestamp from another column

Hi Dieter,

Is there a way to add the duration in second to a date column(start_time) to determine the end time?