pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using dates
3+ messages / 2 participants
[nested] [flat]

* Using dates
@ 1999-08-02 08:01  maxsbox <maxsbox@scds.co.za>
  0 siblings, 1 reply; 3+ messages in thread

From: maxsbox @ 1999-08-02 08:01 UTC (permalink / raw)
  To: pgsql-sql@hub.org

Subject: 
             Re: pgsql-sql-digest V1 #308
       Date: 
             Mon, 02 Aug 1999 09:26:39 +0200
       From: 
             maxsbox <maxsbox@scds.co.za>
         To: 
             pgsql-sql@hub.org
 References: 
             1




> 
> Date: Sun, 1 Aug 1999 19:57:36 +0300
> From: Herouth Maoz <herouth@oumail.openu.ac.il>
> Subject: Re: [SQL] date_arithmetic revisited
> 
> At 17:15 +0300 on 30/07/1999, Thomas Good wrote:
> 
> > Is there a way/function that allows me to do simple date arithmetic,
> > ala, `SELECT $enddate - startdate;' ?
> 
> Sure. Nike.
> 
> I mean, just do it. What you get from it depends, of course, on the type of
> the fields. If they are of type date, you get the round number of days
> between them (the result is of type int4):
> 
> testing=> select d_start, d_end, d_end - d_start from test2;
>    d_start|     d_end|?column?
> - ----------+----------+--------
> 01-15-1969|08-01-1999|   11155
> 07-14-1999|07-18-1999|       4
> 04-13-1998|03-12-1998|     -32
> 12-01-1999|12-01-1999|       0
> (4 rows)
> 
> If they are of type datetime, you get a timespan describing the difference:
> 
> testing=> select t_end - t_start as result from test1;
> result
> - ----------------------
> @ 11154 days 23 hours
> @ 4 days
> @ 31 days 23 hours ago
> @ 0
> (4 rows)
> 
> If this doesn't suit you, you can take the date_part( 'day', ... ) from the
> above operation, but it truncates rather than rounds. The result is float8,
> BTW.
> 
> Herouth
> 
> - --

I tried to do a query using SELECT ..... WHERE <date1> > <date2>

the response follows:

NOTICE: there is no operator > for types date and int4. You will have to
retype this query using an explicit cast, or you will have to define the
operator for date and int4 using CREATE OPERATOR


I am using pgsql 6.2.  Do later versions have this facility.

I have tried Herouth's query from above and it works.  I tried d_start -
10000 but it resulted in another date. d_start - date(6-6-1966) or
d_start - 6-6-1966 will not work.  I have looked throught the available
functions in the user manual, but cannot find anything suitable.  That
int4 is also precluded narrows the options somewhat.  Can you suggest a
work around as the expected use for my data base will make heavy use of
this. ie at least half the queries will bracket a span of dates.

regards

Max Wood



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

* Re: [SQL] Using dates
@ 1999-08-02 09:09  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: maxsbox <maxsbox@scds.co.za>
  0 siblings, 1 reply; 3+ messages in thread

From: Herouth Maoz @ 1999-08-02 09:09 UTC (permalink / raw)
  To: maxsbox <maxsbox@scds.co.za>; pgsql-sql@hub.org

At 11:01 +0300 on 02/08/1999, maxsbox wrote:


> I am using pgsql 6.2.  Do later versions have this facility.
>
> I have tried Herouth's query from above and it works.  I tried d_start -
> 10000 but it resulted in another date. d_start - date(6-6-1966) or
> d_start - 6-6-1966 will not work.  I have looked throught the available
> functions in the user manual, but cannot find anything suitable.  That
> int4 is also precluded narrows the options somewhat.  Can you suggest a
> work around as the expected use for my data base will make heavy use of
> this. ie at least half the queries will bracket a span of dates.

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

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





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

* Re: [SQL] Using dates
@ 1999-08-03 06:16  maxsbox <maxsbox@scds.co.za>
  parent: Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 0 replies; 3+ messages in thread

From: maxsbox @ 1999-08-03 06:16 UTC (permalink / raw)
  To: pgsql-sql@hub.org

> 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



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


end of thread, other threads:[~1999-08-03 06:16 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-02 08:01 Using dates maxsbox <maxsbox@scds.co.za>
1999-08-02 09:09 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-03 06:16   ` maxsbox <maxsbox@scds.co.za>

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