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: Using dates
Date: Mon, 02 Aug 1999 10:01:35 +0200
Message-ID: <37A5505F.7D25BBDA@scds.co.za> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <37A5505F.7D25BBDA@scds.co.za>
Permalink:  ../37A5505F.7D25BBDA@scds.co.za/
Also on:    postgresql.org/message-id/37A5505F.7D25BBDA@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: Using dates
  In-Reply-To: <37A5505F.7D25BBDA@scds.co.za>
  References: <37A5505F.7D25BBDA@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