pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ulf Mehlig <umehlig@uni-bremen.de>
To: mathera@woody.agvic.gov.au
Cc: pgsql-novice@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] (Fwd) Hi and some advice on Dates
Date: Sun, 8 Nov 1998 10:22:04 +0100
Message-ID: <199811080922.KAA02179@uni-bremen.de> (raw)
In-Reply-To: <104E3CB13D06@woody.agvic.gov.au>
References: <104E3CB13D06@woody.agvic.gov.au>

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
  ~~~~~~~~~~~~~~~~~~~~



view thread (4+ messages)  latest in thread

Message-ID: <199811080922.KAA02179@uni-bremen.de>
Permalink:  ../199811080922.KAA02179@uni-bremen.de/
Also on:    postgresql.org/message-id/199811080922.KAA02179@uni-bremen.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: umehlig@uni-bremen.de, mathera@woody.agvic.gov.au, pgsql-novice@postgreSQL.org
  Subject: Re: [SQL] (Fwd) Hi and some advice on Dates
  In-Reply-To: <199811080922.KAA02179@uni-bremen.de>
  References: <104E3CB13D06@woody.agvic.gov.au> <199811080922.KAA02179@uni-bremen.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox