469,366 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

How calculate date/time difference in DB2

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
4 49218
"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
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
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
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.

Similar topics

5 posts views Thread by Peter | last post: by
4 posts views Thread by Vivek Sharma | last post: by
2 posts views Thread by Lad | last post: by
3 posts views Thread by bbawa1 | last post: by
3 posts views Thread by Steve | last post: by
3 posts views Thread by Steve | last post: by
15 posts views Thread by student4lifer | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.