pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [SQL] (Fwd) Hi and some advice on Dates
4+ messages / 3 participants
[nested] [flat]

* Re: [SQL] (Fwd) Hi and some advice on Dates
@ 1998-11-08 09:22  Ulf Mehlig <umehlig@uni-bremen.de>
  parent: Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
  2 siblings, 0 replies; 4+ messages in thread

From: Ulf Mehlig @ 1998-11-08 09:22 UTC (permalink / raw)
  To: mathera@woody.agvic.gov.au; +Cc: pgsql-novice@postgreSQL.org; 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
  ~~~~~~~~~~~~~~~~~~~~



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [NOVICE] (Fwd) Hi and some advice on Dates
@ 1998-11-08 10:36  W.H. Dekker <wybo@servaly.hobby.nl>
  parent: Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
  2 siblings, 0 replies; 4+ messages in thread

From: W.H. Dekker @ 1998-11-08 10:36 UTC (permalink / raw)
  To: mathera@woody.agvic.gov.au; +Cc: pgsql-novice@postgreSQL.org; pgsql-sql

Andrew Mather wrote: 

> Could anyone help with the following: (I have not fixed data type yet,
> I will be guided by any info I can get) 
> 
> How to query a table and look for any records where 
> the value in a date field is greater than (say) 10 days ago
> 
> 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 ?)

Use Perl as a database interface and have a look at de Perl module
Date::Manip. It has all you need, and more.

-- 
Best regards,
Wybo
===================Servalys Analytical Chemistry Services=================
Wybo H. Dekker        | Deilsedijk 60                 | tel +31-345-652164
wybo@servaly.hobby.nl | 4158 CH Deil, The Netherlands | fax +31-345-652383



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* (Fwd) Hi and some advice on Dates
@ 1998-11-08 15:41  Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
  0 siblings, 3 replies; 4+ messages in thread

From: Andrew Mather (Powered By Linux) @ 1998-11-08 15:41 UTC (permalink / raw)
  To: pgsql-novice@postgreSQL.org; pgsql-sql

G'day All,

I am pretty new to postgres and am using it as a backend for DB's
accessed via the web and Perl DBI.  All this works really well and I
have a few db's up and running on our Intranet at work, where they're
quite heavily used.

My current project requires me to do some date calculations and I'm
stumped !

Could anyone help with the following: (I have not fixed data type yet,
I will be guided by any info I can get) 

How to query a table and look for any records where 
the value in a date field is greater than (say) 10 days ago

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 ?)

I've searched the archives without success and the doc's don't give
much away as far as this type of thing goes.

Thanks in anticipation.
Andrew
--------------------------------------------------------
Andrew Mather:  System Administrator, VIAS 
mathera@woody.agvic.gov.au / amsm@deakin.edu.au
http://www.deakin.edu.au/~amsm/index.html
Ph: 03 92174342 (BH), Mob 0413 009 761
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Linux: it's not just for breakfast anymore.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [NOVICE] (Fwd) Hi and some advice on Dates
@ 1998-11-09 10:25  Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
  parent: Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
  2 siblings, 0 replies; 4+ messages in thread

From: Andrew Mather (Powered By Linux) @ 1998-11-09 10:25 UTC (permalink / raw)
  To: W.H. Dekker <wybo@servaly.hobby.nl>; +Cc: pgsql-novice@postgreSQL.org; pgsql-sql

G'day ,

Thanks for your reply.

> > How to query a table and look for any records where 
> > the value in a date field is greater than (say) 10 days ago
> > 
> > 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 ?)
> 
> Use Perl as a database interface and have a look at de Perl module
> Date::Manip. It has all you need, and more.
> 
Will have a look !  I am using perl for an interface to this db 
anyway.  I didn't realise there was a date module !

Andrew
--------------------------------------------------------
Andrew Mather:  System Administrator, VIAS 
mathera@woody.agvic.gov.au / amsm@deakin.edu.au
http://www.deakin.edu.au/~amsm/index.html
Ph: 03 92174342 (BH), Mob 0413 009 761
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
NEWS! Police begin campaign to run down jaywalkers   

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-11-09 10:25 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-08 15:41 (Fwd) Hi and some advice on Dates Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>
1998-11-08 09:22 ` Ulf Mehlig <umehlig@uni-bremen.de>
1998-11-08 10:36 ` W.H. Dekker <wybo@servaly.hobby.nl>
1998-11-09 10:25 ` Andrew Mather (Powered By Linux) <mathera@woody.agvic.gov.au>

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