How calculate date/time difference in DB2

[lenygold via DBMonster.com]

I found this example in MYSQL:
create table events
( id integer not null primary key
, datetime_start datetime not null
, datetime_end datetime not null
);
insert into events values
( 1, '2006-09-09 14:00', '2006-09-09 16:00' )
,( 2, '2006-09-10 09:00', '2006-09-10 17:00' )
,( 3, '2006-09-11 13:30', '2006-09-11 14:45' )
,( 4, '2006-09-12 09:00', '2006-09-15 17:00' );

select id
, datetime_start
, datetime_end
, datetime_end - datetime_start as diff
from events
id datetime_start datetime_end diff
1 2006-09-09 14:00 2006-09-09 16:00 20000
2 2006-09-10 09:00 2006-09-10 17:00 80000
3 2006-09-11 13:30 2006-09-11 14:45 11500
4 2006-09-12 09:00 2006-09-15 17:00 3080000

Can we get in DB2 date/time difference in one query?
Or we have calculate it separate?
Thank's in advance Leny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200809/1

[Mark A]

"lenygold via DBMonster.com" <u41482@uwewrote in message
news:8997776c4097f@uwe...

>I found this example in MYSQL:
create table events
( id integer not null primary key
, datetime_start datetime not null
, datetime_end datetime not null
);
insert into events values
( 1, '2006-09-09 14:00', '2006-09-09 16:00' )
,( 2, '2006-09-10 09:00', '2006-09-10 17:00' )
,( 3, '2006-09-11 13:30', '2006-09-11 14:45' )
,( 4, '2006-09-12 09:00', '2006-09-15 17:00' );

select id
, datetime_start
, datetime_end
, datetime_end - datetime_start as diff
from events
id datetime_start datetime_end diff
1 2006-09-09 14:00 2006-09-09 16:00 20000
2 2006-09-10 09:00 2006-09-10 17:00 80000
3 2006-09-11 13:30 2006-09-11 14:45 11500
4 2006-09-12 09:00 2006-09-15 17:00 3080000

Can we get in DB2 date/time difference in one query?
Or we have calculate it separate?
Thank's in advance Leny G.

Try the SQL Reference manual Vol 1
http://www-01.ibm.com/support/docvie...id=swg27009727

[Tonkuma]

you can use TIMESTAMPDIFF function.
But, I think that it is not accurate, it is estimation.

Please see here for basic handlings of date/time data types on DB2.
Fun with Dates and Times
http://www.ibm.com/developerworks/db.../0211yip3.html

You may be able to use the folowing expression for exsact difference
of timestamp.
DAYS(datetime_end )*24*60*60 + MICROSECONDS(datetime_end )
-
DAYS(datetime_start)*24*60*60 + MICROSECONDS(datetime_start)

[Tonkuma]

I saw the following descriptions in the article.

Using timestampdiff() is more accurate when the dates are close
together than when they are far apart. If you need a more precise
calculation, you can use the following to determine the difference in
time (in seconds):

(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

For convenience, you can also create an SQL user-defined function of
the above:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
)
@

[Tonkuma]

You can use TIMESTAMPDIFF function.
But, I think that it is not accurate, it is estimation.

Please see here for basic handlings of date/time data types on DB2.
Fun with Dates and Times
http://www.ibm.com/developerworks/db...le/0211yip/021...

You may be able to use the folowing expression for exsact difference
of timestamp.
DAYS(datetime_end )*24*60*60 + MIDNIGHT_SECONDS(datetime_end )
-
DAYS(datetime_start)*24*60*60 + MIDNIGHT_SECONDS(datetime_start)
Also, I saw the follwing descriptions in the article "Fun with Dates
and Times".

Using timestampdiff() is more accurate when the dates are close
together than when they are far apart. If you need a more precise
calculation, you can use the following to determine the difference in
time (in seconds):

(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

For convenience, you can also create an SQL user-defined function of
the above:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (
(DAYS(t1) - DAYS(t2)) * 86400 +
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))
)
@