From: | Ulf Mehlig <umehlig(at)uni-bremen(dot)de> |
---|---|
To: | mathera(at)woody(dot)agvic(dot)gov(dot)au |
Cc: | pgsql-novice(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] (Fwd) Hi and some advice on Dates |
Date: | 1998-11-08 09:22:04 |
Message-ID: | 199811080922.KAA02179@uni-bremen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Andrew Mather <mathera(at)woody(dot)agvic(dot)gov(dot)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(dot)mehlig(at)uni-bremen(dot)de>
%%%%!%%% Projekt "MADAM" <umehlig(at)uni-bremen(dot)de>
%%%% %!% %%%% ----------------------------------------------------
---| %%% MADAM: MAngrove | Center for Tropical Marine
||--%!% Dynamics | Biology
|| And | Fahrenheitstrasse 1
_ /||\_/\_ Management |
/ / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany
~~~~~~~~~~~~~~~~~~~~
From | Date | Subject | |
---|---|---|---|
Next Message | W.H. Dekker | 1998-11-08 10:36:47 | Re: [NOVICE] (Fwd) Hi and some advice on Dates |
Previous Message | Andrew Mather (Powered By Linux) | 1998-11-07 22:55:16 | SQL help |