How to add/subtract date/time components using a calculated interval?

Question

I'd like to get this to work in Teradata:

Updated SQL for better example

select
case
    when 
        current_date between
        cast('03-10-2013' as date format 'mm-dd-yyyy') and
        cast('11-03-2013' as date format 'mm-dd-yyyy')
    then 4
    else 5
end Offset,
(current_timestamp + interval Offset hour) GMT

However, I get an error of Expected something like a string or a Unicode character blah blah. It seems that you have to hardcode the interval like this:

select current_timestamp + interval '4' day

Yes, I know I hardcoded it in my first example, but that was only to demonstrate a calculated result.

If you must know, I am having to convert all dates and times in a few tables to GMT, but I have to account for daylight savings time. I am in Eastern, so I need to add 4 hours if the date is within the DST timeframe and add 5 hours otherwise.

I know I can just create separate update statements for each period and just change the value from a 4 to a 5 accordingly, but I want my query to be dynamic and smart.

Answer 1

Here's the solution:

select
case
    when 
        current_date between
        cast('03-10-2013' as date format 'mm-dd-yyyy') and
        cast('11-03-2013' as date format 'mm-dd-yyyy')
    then 4
    else 5
end Offset,
(current_timestamp + cast(Offset as interval hour)) GMT

You have to actually cast the case statement's return value as an interval. I didn't even know interval types existed in Teradata. Thanks to this page for helping me along:

http://www.teradataforum.com/l081007a.htm

Answer 2

If I understand correctly, you want to multiply the interval by some number. Believe it or not, that's literally all you need to do:

select  current_timestamp                    as right_now
      , right_now +      (interval '1' day)  as same_time_tomorrow
      , right_now + (2 * (interval '1' day)) as same_time_next_day

Intervals have always challenged me for some reason; I don't use them very often. But I've had this little example in my Teradata "cheat sheet" for quite a while.