Re: [SQL] date_arithmetic revisited - Mailing list pgsql-sql

On Sun, 1 Aug 1999, Herouth Maoz wrote:

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

You know, Herouth, I did try this but as usual, concrete thinking did me
in!  I did SELECT '07-01-1999' - '06-01-1999';  to which pg responded
with a request for an explicit cast.

Never thought of dumping dates to a temp table, doing the calcs and then
dropping the table...thanks for the help (again)!  Unfortunately, I get
stuck in the mode of passing values to/from perl rather than letting
Pg do *all* the work.

Here is something, if you don't know already:  Steffen Beyer's Date::Calc 
module is handy for doing date arithmetic in perl.  It is not as neat as
Pg but it beats doing expressions based on epoch seconds.

With his code I use split to convert the date into an array, then use
the subscripted elements as args to pass to the Delta_Days function which
does the calculation:  
$pt_days = Delta_Days ($month1,$day1,$year1,  $month2,$day2,$year2);
This returns an int4 value.  (In my case I reverse the sets of args to 
get a positive value as 1 = admission and 2 = discharge.)

Anyway, thanks alot for the assist.  You parted the clouds of obtuse
thinking once again.  BTW, I think of you occasionally when the wife
and I go out to eat - we fancy two things that exist in your part of
the world: Arack and Falafel...arack in particular has come in 
handy lately.  July was both the hottest and driest month in New York 
City history.  The Williamsburg section blew out (electrical) feeder
cables from usage overload and was without power during the hottest 
stretch...ouch.

Cheers,
Tom

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


------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org                                         Phone:
718-354-5528                                          Fax:   718-354-5056                               
 
/* Member: Computer Professionals For Social Responsibility */