From jt-kirkpatrick@mpsllc.com Thu Apr 29 20:11:22 1999 Received: from s20.highertech.net (root@s20.highertech.net [209.54.120.20]) by hub.org (8.9.3/8.9.1) with ESMTP id QAA27255 for ; Thu, 29 Apr 1999 16:11:22 -0400 (EDT) (envelope-from jt-kirkpatrick@mpsllc.com) Received: from kirkpatrickjt (whiteoak-34.highertech.net [209.140.56.34] (may be forged)) by s20.highertech.net (8.8.6/8.8.6) with SMTP id QAA04158 for ; Thu, 29 Apr 1999 16:10:31 -0400 Received: by localhost with Microsoft MAPI; Thu, 29 Apr 1999 16:13:24 -0400 Message-ID: <01BE925B.352BE160.jt-kirkpatrick@mpsllc.com> From: JT Kirkpatrick To: "'pgsql-sql@hub.org'" Subject: time intervals Date: Thu, 29 Apr 1999 16:13:21 -0400 Organization: MPS, LLC X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have two fields, timein & timeout, both defined as type "time". how can i get the difference between the two?? i am connecting to postgres through access97 and trying to run a simple query to show me timeout, timein, and timeout-timein, but the calculation is returning an error. thanks in advance! jt From mark@office.knowledge.com Fri Apr 30 08:10:11 1999 Received: from office.knowledge.com (IDENT:exim@office.knowledge.com [195.40.167.196]) by hub.org (8.9.3/8.9.1) with ESMTP id EAA05158 for ; Fri, 30 Apr 1999 04:10:11 -0400 (EDT) (envelope-from mark@knowledge.com) Received: from mark (helo=localhost) by office.knowledge.com with local-esmtp (Exim 2.10 #1) id 10d8My-0005BC-00; Fri, 30 Apr 1999 09:10:00 +0100 Date: Fri, 30 Apr 1999 09:10:00 +0100 (BST) From: Mark Jewiss To: JT Kirkpatrick cc: "'pgsql-sql@hub.org'" Subject: Re: [SQL] time intervals In-Reply-To: <01BE925B.352BE160.jt-kirkpatrick@mpsllc.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: Mark Jewiss > I have two fields, timein & timeout, both defined as type "time". how can > i get the difference between the two?? i am connecting to postgres through > access97 and trying to run a simple query to show me timeout, timein, and > timeout-timein, but the calculation is returning an error. In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not sure if it supports this. Syntax is select datediff (day, mydate, 'Jan 10 1999' from table If mydate = Jan 9, then the result is one. I think datediff accepts datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then you'll get the right answers. Just change day to whatever you need. Regards, Mark. -- Mark Jewiss Knowledge Matters Limited From george@flex.ro Fri Apr 30 09:55:50 1999 Received: from flex.flex.ro (IDENT:root@[193.230.255.4]) by hub.org (8.9.3/8.9.1) with ESMTP id FAA15694 for ; Fri, 30 Apr 1999 05:55:50 -0400 (EDT) (envelope-from george@flex.ro) Received: from flex.ro (george.flex.ro [193.230.255.93]) by flex.flex.ro (8.9.1/8.9.1) with ESMTP id LAA30346; Fri, 30 Apr 1999 11:54:25 +0300 Sender: gestoc@flex.flex.ro Message-ID: <37297D89.1B186907@flex.ro> Date: Fri, 30 Apr 1999 12:53:13 +0300 From: George Moga X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.2.2 i586) X-Accept-Language: ro MIME-Version: 1.0 To: JT Kirkpatrick , SQL PostgreSQL Subject: Re: [SQL] time intervals References: <01BE925B.352BE160.jt-kirkpatrick@mpsllc.com> Content-Type: text/html; charset=iso-8859-2 Content-Transfer-Encoding: 7bit JT Kirkpatrick wrote:
I have two fields, timein & timeout, both defined as type "time".  how can
i get the difference between the two??  i am connecting to postgres through
access97 and trying to run a simple query to show me timeout, timein, and
timeout-timein, but the calculation is returning an error.

thanks in advance!  jt


If your field is defined by tipe "time", the date is not important.

In this case you can use the age(datetime, datetime) in this mode:

a. timein < timeout:

test=> select age(datetime('today'::date,'10:00:15'::time), datetime('today'::date,'12:25:24'::time));
age
----------------------------
@ 2 hours 25 mins 9 secs ago
(1 row)

or

b. timein > timeout:
test=> select age(datetime('today'::date,'10:00:15'::time), datetime('today'::date,'2:25:24'::time));
age
-------------------------
@ 7 hours 34 mins 51 secs
(1 row)
 

I use PostgreSQL v6.5.0 (09-04-1999) on Red Hat LINUX 5.2 with 2.2.5 kernel.
-- 
Best,
  George Moga,
  george@flex.ro
  Braila,ROMANIA
  From jose@sferacarta.com Fri Apr 30 13:04:20 1999 Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.1) with ESMTP id JAA35432 for ; Fri, 30 Apr 1999 09:04:20 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id PAA03386; Fri, 30 Apr 1999 15:04:04 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10dEXu-0000U4-00; Fri, 30 Apr 1999 14:45:42 +0000 Message-ID: <3729A5FF.F424F0CD@sferacarta.com> Date: Fri, 30 Apr 1999 14:45:51 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Mark Jewiss CC: JT Kirkpatrick , "'pgsql-sql@hub.org'" Subject: Re: [SQL] time intervals References: Content-Type: multipart/alternative; boundary="------------35A464B01DC19010AE263289" --------------35A464B01DC19010AE263289 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Mark Jewiss ha scritto: > > I have two fields, timein & timeout, both defined as type "time". how can > > i get the difference between the two?? i am connecting to postgres through > > access97 and trying to run a simple query to show me timeout, timein, and > > timeout-timein, but the calculation is returning an error. > > In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not > sure if it supports this. > DATEDIFF is not a SQL command maybe an Oracle or Sybase command. > > Syntax is > > select datediff (day, mydate, 'Jan 10 1999' > from table > > If mydate = Jan 9, then the result is one. I think datediff accepts > datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then > you'll get the right answers. Just change day to whatever you need. > > Regards, > > Mark. > -- > Mark Jewiss > Knowledge Matters Limited You can create an operator for TIME - TIME... -- Arithmetic operations that are permitted by SQL92: -- ----------------------------------------- -- 1st operand|operator|2nd operand|result -- -----------+--------+-----------+-------- -- datetime | - |datetime |interval -- datetime | + |interval |datetime -- datetime | - |interval |datetime -- interval | + |datetime |datetime -- interval | + |interval |interval -- interval | - |interval |interval -- interval | * |number |interval -- interval | / |number |interval -- number | * |interval |interval -- -----------+--------+-----------+-------- --TIME - TIME = INTERVAL------------------------------------------- create function time_mi_time(time,time) returns timespan as ' declare i1 interval; i2 interval; begin i1:= $1; i2:= $2; i1:=i1 - i2; return i1; end;' language 'plpgsql'; create operator - ( leftarg=time, rightarg=time, procedure=time_mi_time, commutator='-', negator='+', restrict=eqsel, join=eqjoinsel ); hygea=> select time '12:30' - time '12:00'; ?column? -------- 00:30:00 (1 row) -- ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose' --------------35A464B01DC19010AE263289 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Mark Jewiss ha scritto:

> I have two fields, timein & timeout, both defined as type "time".  how can
> i get the difference between the two??  i am connecting to postgres through
> access97 and trying to run a simple query to show me timeout, timein, and
> timeout-timein, but the calculation is returning an error.

In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not
sure if it supports this.
 

DATEDIFF is not a SQL command maybe an Oracle or Sybase command.
 
Syntax is

select datediff (day, mydate, 'Jan 10 1999'
from table

If mydate = Jan 9, then the result is one. I think datediff accepts
datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then
you'll get the right answers. Just change day to whatever you need.

Regards,

Mark.
--
Mark Jewiss
Knowledge Matters Limited

You can create an operator for TIME - TIME...

--  Arithmetic operations that are permitted by SQL92:
--  -----------------------------------------
--  1st operand|operator|2nd operand|result
--  -----------+--------+-----------+--------
--  datetime   |   -    |datetime   |interval
--  datetime   |   +    |interval   |datetime
--  datetime   |   -    |interval   |datetime
--  interval   |   +    |datetime   |datetime
--  interval   |   +    |interval   |interval
--  interval   |   -    |interval   |interval
--  interval   |   *    |number     |interval
--  interval   |   /    |number     |interval
--  number     |   *    |interval   |interval
--  -----------+--------+-----------+--------

--TIME - TIME = INTERVAL-------------------------------------------
create function time_mi_time(time,time) returns timespan as '
declare
        i1 interval;
        i2 interval;
begin
        i1:= $1;
        i2:= $2;
        i1:=i1 - i2;
        return i1;
end;' language 'plpgsql';

create operator - (
        leftarg=time,
        rightarg=time,
        procedure=time_mi_time,
        commutator='-',
        negator='+',
        restrict=eqsel,
        join=eqjoinsel
        );

hygea=> select  time '12:30' - time '12:00';
?column?
--------
00:30:00
(1 row)

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
  --------------35A464B01DC19010AE263289-- From jt-kirkpatrick@mpsllc.com Fri Apr 30 13:22:41 1999 Received: from s20.highertech.net (root@s20.highertech.net [209.54.120.20]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA38077 for ; Fri, 30 Apr 1999 09:22:41 -0400 (EDT) (envelope-from jt-kirkpatrick@mpsllc.com) Received: from kirkpatrickjt (whiteoak-34.highertech.net [209.140.56.34] (may be forged)) by s20.highertech.net (8.8.6/8.8.6) with SMTP id JAA01364; Fri, 30 Apr 1999 09:21:16 -0400 Received: by localhost with Microsoft MAPI; Fri, 30 Apr 1999 09:24:09 -0400 Message-ID: <01BE92EB.33C3EA80.jt-kirkpatrick@mpsllc.com> From: JT Kirkpatrick To: "'George Moga'" Cc: "'pgsql-sql@hub.org'" Subject: RE: [SQL] time intervals Date: Fri, 30 Apr 1999 09:24:08 -0400 Organization: MPS, LLC X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit george -- thanks! that worked fine! i was able to substitute my fields "timeout" and "timein" for the times you showed in quotes, and add a "where" condition to the select statement to choose a particular record. now i have to find a way to present it in access97, in a regular time format (1:35 - for 1hr 35mins) -- that or get rid of the "ago" which should be pretty easy! thanks so much for your help! jt -----Original Message----- From: George Moga [SMTP:george@flex.ro] Sent: Friday, April 30, 1999 5:53 AM To: JT Kirkpatrick; SQL PostgreSQL Subject: Re: [SQL] time intervals << File: ATT00000.html; charset = iso-8859-2 >> From michael@fastmail.ca Wed Feb 13 16:58:00 2002 Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DGubE03748 for ; Wed, 13 Feb 2002 11:58:00 -0500 (EST) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id DBDAC2BFD; Wed, 13 Feb 2002 11:53:33 -0500 (EST) MIME-Version: 1.0 Message-Id: <3C6A9A0D.000015.04483@ns.interchange.ca> To: pgsql-sql@postgresql.org Subject: Time Intervals From: "Michael Richards" X-Fastmail-IP: [24.43.130.241] Received: from 24.43.130.241 by fastmail.ca with HTTP; Wed, 13 Feb 2002 16:53:33 +0000 (UTC) Date: Wed, 13 Feb 2002 11:53:33 -0500 (EST) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200202/140 X-Sequence-Number: 6568 I've got a rather odd problem that I can't seem to solve easily with the given date manipulation functions. I've got an expiry timestamp and a renewal interval. If something has expired it gets renewed as the expiry + renewal * n Where n is the smallest number that will cause the calculation to result in the future. So if I've got a resource that is renewed by the hour and it expired last week then I need to add on enough hours so its new expiry will be up to 1 hour in the future. Only trouble is this renewal period can be anything from minutes to months and it may have expired up to 6 months ago. If I could convert the timestamp into a julian of some sort perhaps I could do the math that way. Any ideas? -Michael _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians >From pgsql-sql-owner@postgresql.org Wed Feb 13 13:43:42 2002 Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DIMgE40345 for ; Wed, 13 Feb 2002 13:22:42 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 3412D68A66; Wed, 13 Feb 2002 12:22:42 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 672FC68A61; Wed, 13 Feb 2002 12:22:41 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.34 #1 (Debian)) id 16b43F-0002rr-00; Wed, 13 Feb 2002 12:22:41 -0600 Date: Wed, 13 Feb 2002 12:22:41 -0600 From: "Ross J. Reedstrom" To: Michael Richards Cc: pgsql-sql@postgresql.org Subject: Re: Time Intervals Message-ID: <20020213182241.GB10858@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Michael Richards , pgsql-sql@postgresql.org References: <3C6A9A0D.000015.04483@ns.interchange.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca> User-Agent: Mutt/1.3.27i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200202/141 X-Sequence-Number: 6569 On Wed, Feb 13, 2002 at 11:53:33AM -0500, Michael Richards wrote: > I've got a rather odd problem that I can't seem to solve easily with > the given date manipulation functions. > > I've got an expiry timestamp and a renewal interval. If something has > expired it gets renewed as the expiry + renewal * n > Where n is the smallest number that will cause the calculation to > result in the future. > > So if I've got a resource that is renewed by the hour and it expired > last week then I need to add on enough hours so its new expiry will > be up to 1 hour in the future. Only trouble is this renewal period > can be anything from minutes to months and it may have expired up to > 6 months ago. > > If I could convert the timestamp into a julian of some sort perhaps I > could do the math that way. > > Any ideas? Hmm, If I undestand your problem correctly, it's actually pretty easy: you just need to see if expiry is in the past, and if it is, set it to current_timestamp + renewal_interval. If your doing the license expired detection in the frontend in a procedural language, just do a simple update. If you want to hide all that in the backend, you _still_ probably need to use a procedural language, such as pgpsql. Do you want to actually update the databse table with a new expiry, or just calculate one on the fly? Ross From jason.earl@simplot.com Wed Feb 13 19:24:45 2002 Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DJOhE58386 for ; Wed, 13 Feb 2002 14:24:45 -0500 (EST) (envelope-from earlj@xanadu.simplot.com) Received: from xanadu.simplot.COM (151.113.0.132) by charon.simplot.com (MX V5.1-A AnGp) with SMTP; Wed, 13 Feb 2002 12:23:27 -0700 Received: from npa01zz001.simplot.com by xanadu.simplot.COM; (5.65v4.0/1.1.8.2/20Aug97-1116AM) id AA19436; Wed, 13 Feb 2002 12:23:27 -0700 Received: from earlj by npa01zz001.simplot.com with local (Exim 3.34 #1 (Debian)) id 16b502-0006Fq-00; Wed, 13 Feb 2002 12:23:26 -0700 To: "Michael Richards" CC: pgsql-sql@postgresql.org Subject: Re: Time Intervals References: <3C6A9A0D.000015.04483@ns.interchange.ca> From: Jason Earl Date: 13 Feb 2002 12:23:26 -0700 In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca> Message-ID: <874rklnq35.fsf@npa01zz001.simplot.com> Lines: 54 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200202/144 X-Sequence-Number: 6571 PostgreSQL has all kinds of nifty date math tools. For example, processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", CURRENT_TIMESTAMP + interval '1 hour' AS "LATER"; NOW | LATER ------------------------+------------------------ 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07 (1 row) It seems to me that what you really want isn't to add an interval value to your expiry timestamp, but rather you need to add the interval value to the current timestamp. The cool thing is that intervals like '1 week', '30 days', '5 minutes' all work like you would expect. So when you update your records simply do something like this: UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' WHERE ... I hope this was helpful. Jason "Michael Richards" writes: > I've got a rather odd problem that I can't seem to solve easily with > the given date manipulation functions. > > I've got an expiry timestamp and a renewal interval. If something has > expired it gets renewed as the expiry + renewal * n > Where n is the smallest number that will cause the calculation to > result in the future. > > So if I've got a resource that is renewed by the hour and it expired > last week then I need to add on enough hours so its new expiry will > be up to 1 hour in the future. Only trouble is this renewal period > can be anything from minutes to months and it may have expired up to > 6 months ago. > > If I could convert the timestamp into a julian of some sort perhaps I > could do the math that way. > > Any ideas? > > -Michael > _________________________________________________________________ > http://fastmail.ca/ - Fast Secure Web Email for Canadians > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html From michael@fastmail.ca Wed Feb 13 19:38:12 2002 Received: from mail.interchange.ca (ns.interchange.ca [216.126.79.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DJc9E63227 for ; Wed, 13 Feb 2002 14:38:12 -0500 (EST) (envelope-from michael@fastmail.ca) Received: by mail.interchange.ca (Fastmailer, from userid 555) id 407F02A9D; Wed, 13 Feb 2002 14:37:48 -0500 (EST) MIME-Version: 1.0 Message-Id: <3C6AC08C.0000A1.04458@ns.interchange.ca> To: jason.earl@simplot.com Subject: Re: Time Intervals Cc: pgsql-sql@postgresql.org From: "Michael Richards" X-Fastmail-IP: [24.43.130.241] Received: from 24.43.130.241 by fastmail.ca with HTTP; Wed, 13 Feb 2002 19:37:48 +0000 (UTC) Date: Wed, 13 Feb 2002 14:37:48 -0500 (EST) Content-Transfer-Encoding: 7bit Content-Type: Text/Plain X-Archive-Number: 200202/145 X-Sequence-Number: 6572 That is the trivial case. The difficulty arises when the expiry plus the interval is still less than the current timestamp. In my original description of the problem this is the 'n' part of the equation. The only solution I can think of is if I can convert a date into a number of some sort and then just use normal math on it. I really need: | now - then | | ---------- | * (period+1) + then |_ period _| That's basically find the amount of time since it's expired and determine the number of periods between the expiry and now and take the floor of it. Multiply that by the number of periods plus 1 to get the new expiry. Since there is no way to divide 2 intervals in postgres I believe I need to find a way to turn a timestamp and an interval into a number. -Michael > PostgreSQL has all kinds of nifty date math tools. For example, > > > processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", > CURRENT_TIMESTAMP + interval '1 hour' AS "LATER"; > > NOW | LATER > ------------------------+------------------------ > 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07 > (1 row) > > It seems to me that what you really want isn't to add an interval > value to your expiry timestamp, but rather you need to add the > interval value to the current timestamp. The cool thing is that > intervals like '1 week', '30 days', '5 minutes' all work like you > would expect. > > So when you update your records simply do something like this: > > UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' > WHERE ... > > I hope this was helpful. > > Jason _________________________________________________________________ http://fastmail.ca/ - Fast Secure Web Email for Canadians >From pgsql-sql-owner@postgresql.org Wed Feb 13 16:21:47 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLAaE92751 for ; Wed, 13 Feb 2002 16:10:37 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 693157; Wed, 13 Feb 2002 13:10:35 -0800 From: "Josh Berkus" Subject: Re: Time Intervals To: "Michael Richards" , jason.earl@simplot.com Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Wed, 13 Feb 2002 13:10:35 -0800 Message-ID: In-Reply-To: <3C6AC08C.0000A1.04458@ns.interchange.ca> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/146 X-Sequence-Number: 6573 Michael, > The only solution I can think of is if I can convert a date into a > number of some sort and then just use normal math on it. I really > need: > > | now - then | > | ---------- | * (period+1) + then > |_ period _| > > That's basically find the amount of time since it's expired and > determine the number of periods between the expiry and now and take > the floor of it. Multiply that by the number of periods plus 1 to get > > the new expiry. > > Since there is no way to divide 2 intervals in postgres I believe I > need to find a way to turn a timestamp and an interval into a number. Hmmm ... yeah, you're right. Do you need the date+time or just the date? If the latter, you can use the DATE data type, which is integer-based. If the former, maybe the core team needs some help implementing * and / operators for TIMESTAMP and INTERVAL ... -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco From tgl@sss.pgh.pa.us Wed Feb 13 21:51:32 2002 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLpVE06221 for ; Wed, 13 Feb 2002 16:51:32 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1DLouf00886; Wed, 13 Feb 2002 16:50:56 -0500 (EST) To: "Michael Richards" cc: jason.earl@simplot.com, pgsql-sql@postgresql.org Subject: Re: Time Intervals In-reply-to: <3C6AC08C.0000A1.04458@ns.interchange.ca> References: <3C6AC08C.0000A1.04458@ns.interchange.ca> Comments: In-reply-to "Michael Richards" message dated "Wed, 13 Feb 2002 14:37:48 -0500" Date: Wed, 13 Feb 2002 16:50:55 -0500 Message-ID: <883.1013637055@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/148 X-Sequence-Number: 6575 "Michael Richards" writes: > The only solution I can think of is if I can convert a date into a > number of some sort and then just use normal math on it. I really > need: > | now - then | > | ---------- | * (period+1) + then > |_ period _| Are you *sure* that's what you want? You can certainly do it that way --- extract(epoch from timestamp), do math, convert back --- but the above only works if the "period" is a constant number of seconds. Intervals like "1 month" cannot be handled as above. Less obviously, intervals like "1 day" cannot be handled that way either (think about daylight savings transitions). There was a thread on this same topic just recently, and I think the conclusion was that the cleanest way to handle real-world interval definitions is to rely on a loop around a timestamp + interval addition operator: while tstamp < now do tstamp := tstamp + interval; This is trivial to program in a plpgsql function, for example, and it's quite cheap as long as you don't let too many periods elapse between updates. regards, tom lane