Re: Difference between two time values in SQL

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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)