RE: Difference between two time values in SQL

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

Oops, I forgot I have my own SECOND() function, and that's why that worked
so well.  My bad.  Here's a standard solution that works at V5R3:

time('00:00:00') + (midnight_seconds(endt)-midnight_seconds(strt)) second 

... but that won't work when the job starts before midnight and ends after
midnight.  To handle that, you could do:

time('00:00:00') +
mod(midnight_seconds(endt)+86400-midnight_seconds(strt),86400) second

I used to go here for all my SQL date/time questions:
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.
html - and sometimes I still do.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
Hard work pays off in the future. Laziness pays off now. 


> I think you meant.
>
> With t1 As
>   (Select CURRENT_TIME a1,
>           CURRENT_TIME + 2 Hours + 63 minute a2
>      From SYSIBM/SYSDUMMY1)
>   Select t1.*
>          Time('00:00:00') + (A2 - A1)
>     From t1
>
> gives
>
>  A1        A2        Time expression
>  16:01:54  19:04:54     03:03:00
>
>
> The other gives a time of zero.
>
> In V6R1 Sql Referance manual it starts on page 160.
>
>
> On Mon, Nov 15, 2010 at 3:41 PM, Needles,Stephen J
> <SNEEDLES@xxxxxxxxxxxxx>wrote:
>
> > Niiiccce Dennis.
> >
> > But where did you find the answer?
> >
> > I could not locate this syntax in the DB2 for i SQL Reference v6r1
> manual,
> > which is my usual "goto" for things like this.  I would like to know
> the
> > rest of this syntax.
> >
> > Steve Needles
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> > midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
> > Sent: Monday, November 15, 2010 3:58 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: RE: Difference between two time values in SQL
> >
> > OK, I give:
> >
> > TIME('00:00:00') + (SECOND(END_TIMESTAMP) - SECOND(START_TIMESTAMP))
> > SECONDS
> >
> > Yes, it's that easy.
> >
> >
> > Dennis Lovelady
> > http://www.linkedin.com/in/dennislovelady
> > --
> > "I have found the best way to give advice to your children is to find
> out
> > what they want and then advise them to do it."
> >        -- Harry S Truman
> >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > > bounces@xxxxxxxxxxxx] On Behalf Of Tom E Stieger
> > > Sent: Monday, November 15, 2010 3:04 PM
> > > To: 'Midrange Systems Technical Discussion'
> > > Subject: RE: Difference between two time values in SQL
> > >
> > > Why don't you just subtract them.  That will give you a numeric
> result
> > > with the result being the hours difference in the first two
> positions,
> > > then the minutes, then the seconds.  You can that use DIGITS,
> CONCAT
> > > and TIME functions to get it to a TIME format.
> > >
> > > with t1 as (select current_time a1,
> > > current_time + 2 hours + 63 minute a2
> > >  from
> > > sysibm.sysdummy1)
> > > select t1.*,
> > > SUBSTRING(DIGITS(A2-A1),1,2)||':'||SUBSTRING(DIGITS(A2-
> > > A1),3,2)||':'||SUBSTRING(DIGITS(A2-A1),5,2),
> > > TIME(SUBSTRING(DIGITS(A2-A1),1,2)||':'||SUBSTRING(DIGITS(A2-
> > > A1),3,2)||':'||SUBSTRING(DIGITS(A2-A1),5,2))
> > >  from t1
> > >
> > > It's ugly, but it works.
> > >
> > > -Tom Stieger
> > > California Fine Wire
> > > Engineer
> > >
> > > -----Original Message-----
> > > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > > bounces@xxxxxxxxxxxx] On Behalf Of Rick.Chevalier@xxxxxxxxxxxxxxx
> > > Sent: Monday, November 15, 2010 9: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)
> > >
> > >
> > >
> > > ________________________________
> > > Privileged and Confidential. This e-mail, and any attachments there
> to,
> > > is intended only for use by the addressee(s) named herein and may
> > > contain privileged or confidential information. If you have
> received
> > > this e-mail in error, please notify me immediately by a return e-
> mail
> > > and delete this e-mail. You are hereby notified that any
> dissemination,
> > > distribution or copying of this e-mail and/or any attachments
> thereto,
> > > is strictly prohibited.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing
> > > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > > unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
> take a
> > > moment to review the archives at
> http://archive.midrange.com/midrange-l.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing
> > > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> =======================================================================
> =======
> > This communication, including attachments, is confidential, may be
> subject
> > to legal privileges, and is intended for the sole use of the
> addressee. Any
> > use, duplication, disclosure or dissemination of this communication,
> other
> > than by the addressee, is prohibited. If you have received this
> > communication in error, please notify the sender immediately and
> delete or
> > destroy this communication and all copies.
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.