pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Richards <michael@fastmail.ca>
To: jason.earl@simplot.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Time Intervals
Date: Wed, 13 Feb 2002 14:37:48 -0500 (EST)
Message-ID: <3C6AC08C.0000A1.04458@ns.interchange.ca> (raw)

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 <pgsql-sql@postgresql.org>; Wed, 13 Feb 2002 16:10:37 -0500 (EST)
	(envelope-from josh@agliodbs.com)
Received: from [209.10.40.250] (account <josh@agliodbs.com>)
  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" <josh@agliodbs.com>
Subject: Re: Time Intervals
To: "Michael Richards" <michael@fastmail.ca>, 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: <web-693157@davinci.ethosmedia.com>
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



view thread (9+ messages)  latest in thread

Message-ID: <3C6AC08C.0000A1.04458@ns.interchange.ca>
Permalink:  ../3C6AC08C.0000A1.04458@ns.interchange.ca/
Also on:    postgresql.org/message-id/3C6AC08C.0000A1.04458@ns.interchange.ca

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: michael@fastmail.ca, jason.earl@simplot.com
  Subject: Re: Time Intervals
  In-Reply-To: <3C6AC08C.0000A1.04458@ns.interchange.ca>

* 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