# Thread: Calculate time difference between 2 timestamp fileds

1. Registered User
Join Date
Oct 2003
Posts
93

## Unanswered: Calculate time difference between 2 timestamp fileds

I ran the sql like this.

select, TIME(FieldA), TIME(FieldB)
(Time(Field A) - Time(FieldB)) as NET

The results were

18:35:47, 18:32:45, 302

I want to show the NET as 3:02 not 302.

2. Registered User
Join Date
Jan 2003
Posts
4,310
You probable should create a UDF to do what you want:

CREATE FUNCTION PRODUCTION.TIME_DIFF(T1 TIME,T2 TIME)
RETURNS VARCHAR(8) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT
BEGIN ATOMIC
DECLARE tDIFF DECIMAL(6,0);
DECLARE T_STRING VARCHAR(8);
DECLARE HH char(2);
DECLARE MM char(2);
DECLARE SS char(2);

SET tDIFF = T1 - T2;

SET HH = SUBSTR(DIGITS(HOUR(tDIFF)),9);
SET MM = SUBSTR(DIGITS(MINUTE(tDIFF)),9);
SET SS = SUBSTR(DIGITS(SECOND(tDIFF)),9);

SET t_STRING = HH || ':' || MM || ':' || SS;

RETURN T_STRING;

END@

HTH

Andy

3. :-)
Join Date
Jun 2003
Location
Posts
5,516

## Re: Calculate time difference between 2 timestamp fileds

Originally posted by mgupta
I ran the sql like this.

select, TIME(FieldA), TIME(FieldB)
(Time(Field A) - Time(FieldB)) as NET

The results were

18:35:47, 18:32:45, 302

I want to show the NET as 3:02 not 302.

The result of subtracting one time value from another is timestamp duration, which is a DECIMAL(20,6) in the format:

yyyymmddhhmmss.zzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and microseconds.

This basically means that if you want any fancy formatting of this number you'll have to do it yourself. For example, you could convert it to a string and then parse the string to extract minutes and seconds.

4. Registered User
Join Date
Jan 2003
Posts
4,310

## Re: Calculate time difference between 2 timestamp fileds

Originally posted by n_i
The result of subtracting one time value from another is timestamp duration, which is a DECIMAL(20,6) in the format:

yyyymmddhhmmss.zzzzzz, where yyyy, mm, dd, hh, mm, ss, and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and microseconds.

This basically means that if you want any fancy formatting of this number you'll have to do it yourself. For example, you could convert it to a string and then parse the string to extract minutes and seconds.
Actually subtracting times (not timestamps) is a time duration which is DECIMAL (6,0). Subtracting timestamps is a timestamp duration as you describe.

Andy

5. Super Moderator
Join Date
Aug 2001
Location
UK
Posts
4,650

## Re: Calculate time difference between 2 timestamp fileds

will output from TIMESTAMPDIFF function make the customization any easier ?

Cheers
Sathyaram

6. Registered User
Join Date
Oct 2003
Posts
93
thanks for the responses.
I tried using timestampdiff. It works but the arguments options a re limited.
ie it will either give in min or seconds etc. No HH:MMS format..

anyway thanks a ton to all for the great responses.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•