Thread: date_arithmetic revisited

Herouth,

I am doing this:

psql tracker -qtc "select date_part('epoch', '$edate'::datetime);" > .arg1
psql tracker -qtc "select date_part('epoch', '$sdate'::datetime);" > .arg2
date1=`cat .arg1`;
date2=`cat .arg2`;
psql tracker -qtc "select ($date1 - $date2) / 60 / 60 / 24;"
to calculate intervals...this is census stuff.
How many Pt Days per bed, etc.

Is there a way/function that allows me to do simple date arithmetic,
ala, `SELECT $enddate - startdate;' ?

Cheers and thanks,
Tom


------- 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 */ 

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

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

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 */