CODEWITHSUNDEEP
sqltimestampteradata-sql-assistant
sertine
sertine

Reputation: 93

How to calculate differences of two columns with timestamp format?

I need to create a column differences of two columns with timestamp format.

I have col1 and col2, need to generate column "diff(hous)", as shown in picture highlighted part. Thanks.

pls see example

Upvotes: 1

Views: 1703

Answers (2)

Fred
Fred

Reputation: 2055

Note that your "diff(hours)" values actually represent days, not hours.

Teradata does not provide a native timestamp difference function but you can calculate by extracting the parts and doing simple math:

select col1, col2,
cast((cast(col1 as date) - cast(col2 as date)) as float)
+cast((extract(hour from col1) - extract(hour from col2)) as float)/24
+cast((extract(minute from col1) - extract(minute from col2)) as float)/24/60
+cast((extract(second from col1) - extract(second from col2)) as float)/24/60/60
as diff_days
from myTable;

You could put the calculation into a SQL UDF if you need to use it multiple places.

Upvotes: 0

Askam Eyra
Askam Eyra

Reputation: 21

Calculate the time difference between two timestamps in mysql

So you need to first create an empty new column, and then populate it by doing a TIMESTAMPDIFF. Something like that :

UPDATE table SET newcolumn = TIMESTAMPDIFF(HOUR,col1,col2);

Something like that

Upvotes: 1

Related Questions