Re: TIMESTAMP SUBTRACTION (retry, list is bouncing emails)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



> I need to compare this difference with a numeric value in my WHERE clause
> like this
>
> WHERE
>   TIMESTAMP('05-21-2003 00:00:00','mm-dd-yyyy') -
>   TO_TIMESTAMP('04-30-2003 00:00:00','mm-dd-yyyy') > 30
>
> But if I do this....I get wrong results. How do I do such a comparison? Or
> How to I get a Numeric value after the subtraction?

Check out the EXTRACT function, which can give you the number of days:

CREATE TABLE timetest (
 mike TIMESTAMP,
 ike  TIMESTAMP
);

INSERT INTO timetest (mike, ike) VALUES (now(), now()-1);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-2);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-3);
INSERT INTO timetest (mike, ike) VALUES (now(), now()-4);

SELECT * FROM timetest WHERE EXTRACT(day FROM mike - ike) > 2;

If you don't want to limit yourself to days, you can also convert everything
to seconds, which has the nice effect that any interval such as minute,
hour, and day can be expressed as a number of seconds.

Note that "epoch" does not return an integer, so we throw in a ROUND to
neaten things up.


* Number of seconds between the two:

SELECT ROUND(EXTRACT(epoch FROM mike-ike)) FROM timetest;
 round
- --------
 118727
 205127
 291527
 377927


* Number of minutes between the two:

SELECT ROUND(EXTRACT(epoch FROM mike-ike)/60) FROM timetest;
 round
- -------
  1979
  3419
  4859
  6299


* Number of hours between the two:

SELECT ROUND(EXTRACT(epoch FROM mike-ike)/(60*60)) FROM timetest;
 round
- -------
    33
    57
    81
   105



* Number of days between the two, expressed both ways:

SELECT ROUND(EXTRACT(epoch FROM mike-ike)/(60*60*24)) AS uno,
       EXTRACT(days FROM mike-ike) AS dos FROM timetest;

 uno | dos
- -----+-----
   1 |   1
   2 |   2
   3 |   3
   4 |   4


Note that "days" is the only one where they will be equivalent: do not
try the following:

SELECT EXTRACT(hours FROM mike-ike) FROM timetest;

as it will not give you the total hours, but only the difference in hours as if
both times were on the same day. "Days" is safe to use as an absolute difference
because intervals are not measured in units higher than a day.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305210851
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+y30IvJuQZxSWSsgRAlbvAKCBlDCKY7UnlZXi7L/JDyeVMI35QwCfdXs6
yjjqi0wk6cGRaW/ub0aQyCQ=
=W5ny
-----END PGP SIGNATURE-----