Time difference in seconds

Database
Enthusiast

Time difference in seconds

hello all,
i am trying to get the difference between two timestamp fields in seconds
when i use the query
SELECT GMT_END_TIME, GMT_START_TIME,
(GMT_END_TIME - GMT_START_TIME) SECOND(4) AS SECONDS4
FROM ECCDEVWORK.INTERACTION_FACT_W1
SAMPLE 10

it works fine but when i run it on the whole set it fails with an error

SELECT GMT_END_TIME, GMT_START_TIME,
(GMT_END_TIME - GMT_START_TIME) SECOND(4) AS SECONDS4
FROM ECCDEVWORK.INTERACTION_FACT_W1

7453: Interval field overflow.
Output directed to Answerset window

and when i increase the precision to 5 or 6
SELECT GMT_END_TIME, GMT_START_TIME,
(GMT_END_TIME - GMT_START_TIME) SECOND(5) AS SECONDS4
FROM ECCDEVWORK.INTERACTION_FACT_W1
it fails with
3706: Syntax error: Invalid INTERVAL Precision value.
Output directed to Answerset window

can some one help?
I need to get differece in seconds.

3 REPLIES
Enthusiast

Re: Time difference in seconds

Feroz,

You'll have to do "minute(4) to second" or "hour(4) to second" and then break it up and multiply the minutes by 60 or if you use "hour(4) to second", multiply the hours by 3600, the minutes by 60, and then add in the seconds:

select substr(cast((GMT_END_TIME - GMT_START_TIME hour(4) to second) as char(18)),1,5) * 3600 +
substr(cast((GMT_END_TIME - GMT_START_TIME hour(4) to second) as char(18)),7,2) * 60 +
substr(cast((GMT_END_TIME - GMT_START_TIME hour(4) to second) as char(18)),10)

Hope this helps.

Regards,
Barry

Enthusiast

Re: Time difference in seconds

Thanks Barry,
we (my Colleagu) also figured out a way by using the extract funciton to extract hours , minutes and seconds and doing the similar calculation.

SELECT
GMT_END_TIME,
GMT_START_TIME,
(GMT_END_TIME - GMT_START_TIME) MINUTE(4) TO SECOND AS MINSEC,
EXTRACT(MINUTE FROM minsec) AS MIN_,
EXTRACT(SECOND FROM minsec) AS SEC_,
MIN_ * 60 + SEC_ AS CallInterval

we just used the minute(4) to second because we dont expect a call to extend more than a day. :-)

which one is better to use "extract" or "substr"

Thanks once again for the query that you sent.

Enthusiast

Re: Time difference in seconds

"EXTRACT" is probably faster and is more straightforward. I tend to forget about some of the newer functions. That'll teach me for getting old!