Re: Difference between two time values in SQL

Rick

I'm not sure what the engine would do here. If I had a value that used a calculation, I'd probably put in a common-table-expression, using WITH ... AS, then I'd have a field name to use where the calculation is now. This was just a quick tossup example, anyhow. :-)

Again, in real life, time('02.15.45') would likely be a time-type field.

Vern

On 11/16/2010 7:35 AM, Rick.Chevalier@xxxxxxxxxxxxxxx wrote:

Thanks Vern.

I got interrupted yesterday and didn't get to complete my testing. I wasn't sure but it looked like the subtraction was giving me a number of seconds. Looks like it was giving me what I wanted all along, I just didn't recognize it.

Question on your second example:
SELECT current time, time('02.15.45'), current time - time('02.15.45'), second(current time - time('02.15.45')) FROM sysibm/sysdummy1

I have always tried to avoid repeating calculations in my SQL statements to cut down on the amount of work that needed to be done. Does the query engine actually perform the subtraction twice for each record or is it smart enough to know it is the same calculation and simply use the result of the first occurrence over again?



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Tuesday, November 16, 2010 6:31 AM
To: Midrange Systems Technical Discussion
Subject: Re: Difference between two time values in SQL

Rick

You can use direct subtraction of the various date/time/timestamp data types. I ran the following statement -

select current time, time('02.15.45'), current time - time('02.15.45') from sysibm/sysdummy1

and got this result.

CURRENT TIME TIME ( '02.15.45' ) Numeric Expression
07:12:54 02:15:45 45,709

The numeric value is a set of values - in this case, it is 4 hours, 57 minutes, and 9 seconds.

If you subtract a future time from another, you get a negative number - not so useful! So it'd be helpful to include the date - hence, a timestamp. There, with subtraction, you get this duration thing, with the counts of years, months, days, hours, minutes, seconds, microseconds one after the other - you CAN use the various year, month, etc., functions on the duration, as here -

SELECT current time, time('02.15.45'), current time - time('02.15.45'), second(current time - time('02.15.45')) FROM
sysibm/sysdummy1

CURRENT TIME TIME ( '11.15.45' ) Numeric Expression SECOND
07:20:41 02:15:45 50,456 56

You may already have found all this, just putting in concrete examples to help someone.

BTW, the site posted below mentions the proviso that timestampdiff uses 30-day months and 365-day years to get and "estimate". The link about date/time arithmetic at

http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/topic/com.ibm.etools.iseries.langref2.doc/rbafzmstdatetimearith.htm

explains how they do things with simple subtraction. I don't know if the same assumptions are used as in the timestampdiff function.

HTH

Vern

On 11/15/2010 1:09 PM, Rick.Chevalier@xxxxxxxxxxxxxxx wrote:

This will give me the duration but I can't convert it directly to a time value. I need the result to be in the format HH:MM:SS. Guess I'll have to work out a formula to use the duration and derive the time.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Monday, November 15, 2010 12:12 PM
To: Midrange Systems Technical Discussion
Subject: RE: Difference between two time values in SQL

TimestampDiff:
http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic
=/ com.ibm.etools.iseries.langref2.doc/rbafzmst271.htm



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Monday, November 15, 2010 11:29 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Difference between two time values in SQL

When I see the answer it will probably be obvious but I have spent over two hours trying to create a query to list records including the difference between two time values. I searched the archives and did a Google search but didn't see the answer. Lots of information on differences between dates though.

How does one go about calculating the difference between two time values in an SQL statement? Do they have to be converted to timestamps first?
I want the result to be a time value as well.


Rick Chevalier
IT Software Solutions - Loan Servicing
817-525-7178 (w)