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.orgReferences: 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
