Re: [SQL] (Fwd) Hi and some advice on Dates - Mailing list pgsql-sql

Andrew Mather <mathera@woody.agvic.gov.au> 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              <ulf.mehlig@uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~