|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/7/2008 5:49:36 PM
Posts: 1,
Visits: 7
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
|
(BEGIN_DATE (TIMESTAMP(0))) + ( ADD_HOURS(INTERVAL HOUR) )
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/9/2009 3:13:03 PM
Posts: 5,
Visits: 11
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2 days ago @ 7:45:03 AM
Posts: 39,
Visits: 169
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/9/2009 3:13:03 PM
Posts: 5,
Visits: 11
|
|
|
That one worked perfectly. Thanks for the help!
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/9/2009 3:13:03 PM
Posts: 5,
Visits: 11
|
|
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?
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/9/2009 3:13:03 PM
Posts: 5,
Visits: 11
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/9/2009 3:13:03 PM
Posts: 5,
Visits: 11
|
|
|
I knew there had to be an easier option! That one works and makes cleaner code!
|
|
|
|