Timestamp calculation

Database
Enthusiast

Timestamp calculation

Hi All,

I have

Table 1 with columns

local_timestamp difference
2008-01-02 10:01:47 -360
2008-01-02 23:01:42 -240
2008-01-04 02:10:08 330
2008-01-02 18:04:32 255

local_timestamp defined as timestamp(0) having format ‘yyyy-mm-dd hh:mi:ss’.
difference is a integer column having positive and negative values. All the values in difference column are “Minutes” . For example -360 indicates “- 6 hours” and 255 indicates “4 hours 15 minutes”

Now I have table2 having only one column derived_timestamp. It's defined as timestamp(0).This column will be populated from table 1.

Formula is :

derived_timestamp = local_timestamp – difference

So the Table2 should have the data

local_timestamp (-) difference = Derived_timestamp
2008-01-02 10:01:47 (-) -360 = 2008-01-02 16:01:47
2008-01-02 23:01:42 (-) -240 = 2008-01-03 03:01:42
2008-01-04 02:10:08 (-) 330 = 2008-01-03 20:40:08
2008-01-02 18:04:32 (-) 255 = 2008-01-02 13:49:32

Can you please help me on framing a SQL for this.

Thanks in advance.
1 REPLY
Teradata Employee

Re: Timestamp calculation

How about
select local_timestamp - (difference * interval '1' minute) from table_1;