Data Modeling
Junior Supporter

Calculating hours, minutes, and seconds from two timestamps dates

Hello, in sql server, I have an update that calculate the hours, minutes, and seconds from two timestamps dates. In particular, this is syntax in sql server:

UPDATE OBESE_PAW_JOBHX SET HOURS=CONVERT(INT,(DATEDIFF(SECOND,STARTTIME, ENDTIME))/3600);
UPDATE OBESE_PAW_JOBHX SET MINUTES=CONVERT(INT,(DATEDIFF(SECOND,STARTTIME, ENDTIME)-HOURS*3600)/60);
UPDATE OBESE_PAW_JOBHX SET SECONDS=DATEDIFF(SECOND,STARTTIME, ENDTIME)-HOURS*3600-MINUTES*60 ;

However, what would be the Teradata equivalent of this code, since there is not convert and datediff function in Teradata.

Accepted Solutions
Highlighted
Senior Apprentice

Re: Calculating hours, minutes, and seconds from two timestamps dates

Found it quicker than I thought...

This is a UDF that I wrote to return the number of seconds between two TS values. You can either implement it as a UDF (user defned function) or just take the SQL code from it.

```REPLACE FUNCTION waTSDIFF_SECS (start_ts TIMESTAMP(6),end_ts TIMESTAMP(6))
RETURNS DECIMAL(38,6)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC waTSDIFF_SECS
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
+ 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
+ 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
+  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
);```

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES 4
Senior Apprentice

Re: Calculating hours, minutes, and seconds from two timestamps dates

Hi,

The basics are very simple, you subtract one TS column form another and set the data type to one of the INTERVAL ones.

Something like:

```SEL a.*
,(lastaltertimestamp - createtimestamp) HOUR(4)
,(lastaltertimestamp - createtimestamp) MINUTE(4)
,(lastaltertimestamp - createtimestamp) HOUR(4) TO SECOND
FROM vt1 AS a```

The first one returns the difference in hours, the second in minutes and the third returns something like "   44:42:47.000000", i.e. 44 hours, 42 minutes and 47 seconds.

The possible danger with this comes if the time difference is more than 9999 hours. The INTERVAL HOUR data types will not handle larger values.

Are you likely to hit that situation? If so then you will need to take a similar approach to what you're already doing but use the EXTRACT function to extract hours, minutes and seconds.

(I've got that code somewhere, back in a bit...)

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Senior Apprentice

Re: Calculating hours, minutes, and seconds from two timestamps dates

Found it quicker than I thought...

This is a UDF that I wrote to return the number of seconds between two TS values. You can either implement it as a UDF (user defned function) or just take the SQL code from it.

```REPLACE FUNCTION waTSDIFF_SECS (start_ts TIMESTAMP(6),end_ts TIMESTAMP(6))
RETURNS DECIMAL(38,6)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC waTSDIFF_SECS
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN (86400 * (CAST(end_ts AS DATE) - CAST(start_ts AS DATE))
+ 3600 * (EXTRACT(HOUR FROM end_ts) - EXTRACT(HOUR FROM start_ts))
+ 60 * (EXTRACT(MINUTE FROM end_ts) - EXTRACT(MINUTE FROM start_ts))
+  1 * (EXTRACT(SECOND FROM end_ts) - EXTRACT(SECOND FROM start_ts))
);```

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Supporter

Re: Calculating hours, minutes, and seconds from two timestamps dates

That you for the code. A quick question. When writing out the UDF in Teradata, is it the SQL editor or somewhere else? I'm writing another set of code that has a UDF.

Senior Apprentice

Re: Calculating hours, minutes, and seconds from two timestamps dates

Hi,

Sorry but I'm not sure what you're asking. If you want to know how to create the udf then you can use any tool that you normally use to run SQL commands against TD - TD Studio, SQLA, Bteq etc.

Is that the information that you need?

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com