By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

How calculate date/time difference in DB2

P: n/a
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

Sep 2 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/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
Sep 2 '08 #2

P: n/a
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)
Sep 2 '08 #3

P: n/a
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))
)
@
Sep 2 '08 #4

P: n/a
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))
)
@
Sep 2 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.