Arithmetic on TIME datatype ? 
Author Message
 Arithmetic on TIME datatype ?
Hi,

I would like to do some very simple arithmetic on variables of TIME datatype
in stored SQL/PL

e.g

v_duration = t2 - t1;

where all 3 variables are declared of type TIME

NB. I also see the same problem with a simple query from the CLP
e.g
C:\>db2 select current time - (current time - 1 hour) from sysibm.sysdummy1

1
--------
  10000.

  1 record(s) selected.

what is this answer ???

Thanks.

Paul.



Sun, 31 Jul 2005 17:29:21 GMT
 Arithmetic on TIME datatype ?

Paul Reddin wrote on Wednesday 12 February 2003 10:29:

Quote:
> Hi,

> I would like to do some very simple arithmetic on variables of TIME datatype
> in stored SQL/PL

> e.g

> v_duration = t2 - t1;

> where all 3 variables are declared of type TIME

How should this work?  A time minus another time is a duration and not a time.  
For example: 4am - 3am is 1 hour and not 1am.

Quote:
> NB. I also see the same problem with a simple query from the CLP
> e.g
> C:\>db2 select current time - (current time - 1 hour) from sysibm.sysdummy1

> 1
> --------
>   10000.

>   1 record(s) selected.

> what is this answer ???

The result is a decimal number with the format hhmmss and means that you have
a duration of 1 hour, 0 minutes and 0 seconds.  Have a look here for more
details:

http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/d...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Sun, 31 Jul 2005 18:03:28 GMT
 Arithmetic on TIME datatype ?

Quote:
> Hi,

> I would like to do some very simple arithmetic on variables of TIME datatype
> in stored SQL/PL

> e.g

> v_duration = t2 - t1;

> where all 3 variables are declared of type TIME

> NB. I also see the same problem with a simple query from the CLP
> e.g
> C:\>db2 select current time - (current time - 1 hour) from sysibm.sysdummy1

> 1
> --------
>   10000.

>   1 record(s) selected.

> what is this answer ???

1 Hour

As in

HHMMSS
010000

To return something more sensable, use something like

CREATE FUNCTION F.SECONDS (X TIME, Y TIME)
  RETURNS INTEGER
 LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
 SECOND(X) + 60 * MINUTE(X) + 3600 * HOUR(X) -
 SECOND(Y) - 60 * MINUTE(Y) - 3600 * HOUR(Y)
;
COMMENT ON FUNCTION F.SECONDS IS
 'Retruns the number of seconds between two TIME values'
;

Enjoy
Paul Vernon
Business Intelligence, IBM Global Services



Sun, 31 Jul 2005 18:45:40 GMT
 Arithmetic on TIME datatype ?
I prefer to use MIDNIGHT_SECONDS and DAYS.
For example:
SELECT ( DAYS(to_time)-DAYS(from_time) )*3600*24
       + MIDNIGHT_SECONDS(to_time) - MIDNIGHT_SECONDS(from_time)
  FROM (VALUES ('2003-02-10-18.30.00', '2003-02-11-09.00.00') ) AS
Q(from_time, to_time)
Quote:



> > Hi,

> > I would like to do some very simple arithmetic on variables of TIME datatype
> > in stored SQL/PL

> > e.g

> > v_duration = t2 - t1;

> > where all 3 variables are declared of type TIME

> > NB. I also see the same problem with a simple query from the CLP
> > e.g
> > C:\>db2 select current time - (current time - 1 hour) from sysibm.sysdummy1

> > 1
> > --------
> >   10000.

> >   1 record(s) selected.

> > what is this answer ???

> 1 Hour

> As in

> HHMMSS
> 010000

> To return something more sensable, use something like

> CREATE FUNCTION F.SECONDS (X TIME, Y TIME)
>   RETURNS INTEGER
>  LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
>  SECOND(X) + 60 * MINUTE(X) + 3600 * HOUR(X) -
>  SECOND(Y) - 60 * MINUTE(Y) - 3600 * HOUR(Y)
> ;
> COMMENT ON FUNCTION F.SECONDS IS
>  'Retruns the number of seconds between two TIME values'
> ;

> Enjoy
> Paul Vernon
> Business Intelligence, IBM Global Services



Tue, 02 Aug 2005 09:36:28 GMT
 Arithmetic on TIME datatype ?

Quote:
> I prefer to use MIDNIGHT_SECONDS and DAYS.
> For example:
> SELECT ( DAYS(to_time)-DAYS(from_time) )*3600*24
>        + MIDNIGHT_SECONDS(to_time) - MIDNIGHT_SECONDS(from_time)
>   FROM (VALUES ('2003-02-10-18.30.00', '2003-02-11-09.00.00') ) AS
> Q(from_time, to_time)

Good point.
We also need to cater for microseconds, and a duration > 2^31 seconds, so how
about this...

CREATE FUNCTION F.SECONDS (X TIMESTAMP, Y TIMESTAMP)
  RETURNS BIGINT
 LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
 (DAYS(X) - DAYS(Y)) * BIGINT(86400) +
 MIDNIGHT_SECONDS(X)
 - MIDNIGHT_SECONDS(Y)
 - CASE WHEN MICROSECOND(X) < MICROSECOND(Y) THEN 1 ELSE 0 END
;
COMMENT ON FUNCTION F.SECONDS (TIMESTAMP, TIMESTAMP) IS
 'Returns the whole number of seconds between two TIMESTAMP values.'
;

Humm, although that milliseconds bit won't work correctly for negative
durations.
Maybe it would be best to just divide the following by 1000000

CREATE FUNCTION F.MICROSECONDS (X TIMESTAMP, Y TIMESTAMP)
  RETURNS BIGINT
 LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
 (DAYS(X) - DAYS(Y)) * BIGINT(86400000000) +
 MIDNIGHT_SECONDS(X) * 1000000
 - MIDNIGHT_SECONDS(Y) * 1000000
 + MICROSECOND(X)
 - MICROSECOND(Y)
;
COMMENT ON FUNCTION F.MICROSECONDS (TIMESTAMP, TIMESTAMP) IS
 'Returns the number of microseconds between two TIMESTAMP values.'

?

Regards
Paul Vernon
Business Intelligence, IBM Global Services



Tue, 02 Aug 2005 18:17:38 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Time arithmetic

2. Time arithmetic in Oracle & WebDB

3. TIME Arithmetic

4. Date/Time arithmetic help needed

5. Time arithmetic?

6. Magic Time Arithmetic?

7. Time datatypes, MS-Access front ends and ODBC

8. Time datatype?

9. insert time only into column define as datetime datatype

10. Field with a Time datatype

11. Datatype definitions (sorry no subject 1st time)

12. Date and Time datatypes


 
Powered by phpBB® Forum Software