pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: maxsbox <maxsbox@scds.co.za>
To: pgsql-sql@hub.org
Subject: Re: [SQL] Using dates
Date: Tue, 03 Aug 1999 08:16:19 +0200
Message-ID: <37A68933.EE9E1BF8@scds.co.za> (raw)
References: <l03130300b3cb0752f841@[147.233.159.109]>

> Perhaps if you explain exactly what you want returned from your query I
> will better understand your problem. What you have now describes shows a
> problem in understanding the semantics of date arithmetic.
> 
> 1) Comparing two dates: As far as I know, this worked ever since
>    6.1, which is the first version of Postgres I used. Just make
>    sure that both sides are indeed dates! It sounds to me as if
>    your trouble is that one of the side was mistaken for an
>    integer.
> 
>    ... WHERE d_start < '1999-04-14'::date
> 
>    should work well.
> 
> 2) Subtracting integer from date. The semantics of this operation
>    is usually that the integer is number of days.
> 
>     testing=> select d_start, d_start - 30 from test2;
>        d_start|  ?column?
>     ----------+----------
>     01-15-1969|12-16-1968
>     07-14-1999|06-14-1999
>     04-13-1998|03-14-1998
>     12-01-1999|11-01-1999
>     (4 rows)
> 
>     The result is of type date even if your original d_start was
>     of type datetime and not date.
> 
> 3) Subtracting a date from a date, as I said, gives you either a
>    timespan or an integer, depending on the type of the operands.
>    You must know how to format a correct date. Just writing
>    1999-4-7 without quotation marks, and preferably adding
>    ::date will probably not pass the parser, even. I'm not sure
>    the function date() worked in early versions.
> 
> 4) Another option for date subtraction is the function age().
> 
>     testing=> select d_start, d_end, age( d_end, d_start ) from test2;
>        d_start|     d_end|age
>     ----------+----------+----------------------------------
>     01-15-1969|08-01-1999|@ 30 years 6 mons 16 days 23 hours
>     07-14-1999|07-18-1999|@ 4 days
>     04-13-1998|03-12-1998|@ 1 mon 23 hours ago
>     12-01-1999|12-01-1999|@ 0
>     (4 rows)
> 
>     This function returns a timespan, for date or datetime operands.
>     The main difference between this and the timespan returned
>     with simple datetime subtraction is that it converts to years,
>     months and days, instead of just days and hours.
> 
> 5) Finally, remember that you can compare timespans. Thus, you can
>    ask for all tuples two weeks back or later, for example, using:
> 
>    testing=> SELECT * FROM test1 WHERE ( 'now' - t_end ) < '2 weeks';
>    t_start                     |t_end
>    ----------------------------+----------------------------
>    Wed Jan 15 00:00:00 1969 IST|Sun Aug 01 00:00:00 1999 IDT
>    Wed Dec 01 00:00:00 1999 IST|Wed Dec 01 00:00:00 1999 IST
>    (2 rows)
> 
>    Or, in a better way for utilizing indices:
> 
>    testing=> SELECT * FROM test1
>    testing-> WHERE t_end > ( 'now'::datetime - '2 weeks'::timespan );
>    t_start                     |t_end
>    ----------------------------+----------------------------
>    Wed Jan 15 00:00:00 1969 IST|Sun Aug 01 00:00:00 1999 IDT
>    Wed Dec 01 00:00:00 1999 IST|Wed Dec 01 00:00:00 1999 IST
>    (2 rows)
> 
>    You noticed that Dec 01 1999 is in the future? Then makse sure
>    to add alse t_end < 'now'.
> 
> HTH,
> Herouth

Guess this stuff is a long way from the dot notation I learned from
Paradox PAL.

Now busy reading in depth the Programmer Manual.

Can you suggest any further reading

Get the feeling that there are more than myself, reading this digest,
that would appreciate such a tip.

Many thanks for the help - It has definitely headed me in the right
direction.

Max Wood



view thread (3+ messages)

Message-ID: <37A68933.EE9E1BF8@scds.co.za>
Permalink:  ../37A68933.EE9E1BF8@scds.co.za/
Also on:    postgresql.org/message-id/37A68933.EE9E1BF8@scds.co.za

 · 

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: maxsbox@scds.co.za, pgsql-sql@hub.org
  Subject: Re: [SQL] Using dates
  In-Reply-To: <37A68933.EE9E1BF8@scds.co.za>
  References: <l03130300b3cb0752f841@[147.233.159.109]> <37A68933.EE9E1BF8@scds.co.za>

* 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