pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Good <tomg@admin.nrnet.org>
To: Herouth Maoz <herouth@oumail.openu.ac.il>
Cc: PostgreSQL::SQL List <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] date_arithmetic revisited
Date: Mon, 2 Aug 1999 08:54:52 -0400 (EDT)
Message-ID: <Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org> (raw)
In-Reply-To: <l03130307b3ca2a5eb72f@[147.233.159.109]>

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




view thread (3+ messages)

Message-ID: <Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org>
Permalink:  ../Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org/
Also on:    postgresql.org/message-id/Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org

 · 

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: tomg@admin.nrnet.org, herouth@oumail.openu.ac.il
  Subject: Re: [SQL] date_arithmetic revisited
  In-Reply-To: <Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org>
  References: <l03130307b3ca2a5eb72f@[147.233.159.109]> <Pine.LNX.3.96.990802083739.27501A-100000@admin.nrnet.org>

* 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