pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate_arithmetic revisited
3+ messages / 2 participants
[nested] [flat]
* date_arithmetic revisited
@ 1999-07-30 14:15 Thomas Good <tomg@admin.nrnet.org>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas Good @ 1999-07-30 14:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: Herouth Maoz <herouth@oumail.openu.ac.il>
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 */
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] date_arithmetic revisited
@ 1999-08-01 16:57 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Thomas Good <tomg@admin.nrnet.org>
0 siblings, 1 reply; 3+ messages in thread
From: Herouth Maoz @ 1999-08-01 16:57 UTC (permalink / raw)
To: Thomas Good <tomg@admin.nrnet.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] date_arithmetic revisited
@ 1999-08-02 12:54 Thomas Good <tomg@admin.nrnet.org>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 3+ messages in thread
From: Thomas Good @ 1999-08-02 12:54 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: 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 */
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-08-02 12:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-30 14:15 date_arithmetic revisited Thomas Good <tomg@admin.nrnet.org>
1999-08-01 16:57 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-02 12:54 ` Thomas Good <tomg@admin.nrnet.org>
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