Received: from uni-bremen.de (root@moritz100.zfn.uni-bremen.de [134.102.6.140]) by hub.org (8.9.1/8.9.1) with ESMTP id EAA04501; Sun, 8 Nov 1998 04:26:25 -0500 (EST) (envelope-from umehlig@uni-bremen.de) Received: (from umehlig@localhost) by uni-bremen.de (8.9.1/8.9.1) id KAA02179; Sun, 8 Nov 1998 10:22:04 +0100 Date: Sun, 8 Nov 1998 10:22:04 +0100 From: Ulf Mehlig Message-Id: <199811080922.KAA02179@uni-bremen.de> To: mathera@woody.agvic.gov.au CC: pgsql-novice@postgreSQL.org, pgsql-sql@postgreSQL.org In-reply-to: <104E3CB13D06@woody.agvic.gov.au> (mathera@woody.agvic.gov.au) Subject: Re: [SQL] (Fwd) Hi and some advice on Dates References: <104E3CB13D06@woody.agvic.gov.au> Andrew Mather wrote: > How to query a table and look for any records where the value in a > date field is greater than (say) 10 days ago Maybe like this: -- create a(n example) table: => create table xxx (d date, something text); CREATE => insert into xxx values ('09.11.1998', 'bbbbb!'); INSERT 72800 1 => insert into xxx values ('02.11.1998', 'aaaa!'); INSERT 72801 1 => insert into xxx values ('08.11.1998', 'cccc!'); INSERT 72802 1 -- have a look (note that I'm using the 'German' date style) => select * from xxx; d|something ----------+--------- 09.11.1998|bbbbb! 02.11.1998|aaaa! 08.11.1998|cccc! (3 rows) 'today' gives current date when casted ('::' operator) to 'date' type; the calculation of difference is simple: => select * from xxx where d between 'today'::date - 10 and 'today'::date; d|something ----------+--------- 02.11.1998|aaaa! 08.11.1998|cccc! (2 rows) > Calculate the difference between two times on the same day. (I have > experimented with age() and it sort of does wht I want..is there any > other way ?) There are various possibilities, depends on what you want. For calculating another 'datetime', try: => select 'now'::datetime as now, 'now'::datetime - '10 secs'::timespan as ten_s_ago; now |ten_s_ago --------------------------+-------------------------- 08.11.1998 09:59:54.00 MET|08.11.1998 09:59:44.00 MET (1 row) You have to cast to a proper type; e.g., you can't do WRONG! => select 'now'::time as now, WRONG! 'now'::time - '10 secs'::timespan as ten_s_ago; probably because the 'time'-type doesn't know anything about the fact that midnight is the beginning of a new day and everything is set to zero ... For calculating a 'timespan', do => select 'now'::datetime - '08.11.1998 06:00'::datetime as interv; interv ------------------------ @ 4 hours 6 mins 30 secs(1 row) or => select date_part('epoch', 'now'::datetime - '08.11.1998 06:00'::datetime) as interv; interv ------- 7708135 (1 row) if you need the result in seconds. Hope it helps! Ulf -- ====================================================================== %%%%% Ulf Mehlig %%%%!%%% Projekt "MADAM" %%%% %!% %%%% ---------------------------------------------------- ---| %%% MADAM: MAngrove | Center for Tropical Marine ||--%!% Dynamics | Biology || And | Fahrenheitstrasse 1 _ /||\_/\_ Management | / / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany ~~~~~~~~~~~~~~~~~~~~