1

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

sertine
  • 93
  • 1
  • 9
  • Standard SQL does not have time functions - they are implemented by each database provider (SQL Server, Oracle, MySql, etc.). Please add the appropriate tag to know which functions you need, – D Stanley Oct 08 '19 at 21:49

2 Answers2

1

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

Askam Eyra
  • 21
  • 7
  • TIMESTAMPDIFF() returns integer values. The requirement is decimal values. – forpas Oct 08 '19 at 21:53
  • Well, then you could use a different unit and make the calcul by yourself maybe ? Something like TIMESTAMPDIFF(FRAC_SECOND,col1,col2)/3600000; – Askam Eyra Oct 08 '19 at 22:28
  • Got this: SELECT Failed. 3706: (-3706)Syntax error: Data Type "FRAC_SECOND" does not match a Defined Type name. – sertine Oct 09 '19 at 17:53
  • Sorry, my bad, try replacing FRAC_SECOND by MICROSECOND https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff Also replace the 360000 by a 3600000000 (has micro second is a smaller unit) You could also use SECOND and divide by 3600, but you will get a smaller precision – Askam Eyra Oct 09 '19 at 22:01
0

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.

Fred
  • 1,916
  • 1
  • 8
  • 16
  • Note that there is a separate `teradata` tag for the database itself; `teradata-sql-assistant` is a client tool. – Fred Oct 10 '19 at 20:45