pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Richards <michael@fastmail.ca>
Cc: jason.earl@simplot.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Time Intervals
Date: Wed, 13 Feb 2002 16:50:55 -0500
Message-ID: <883.1013637055@sss.pgh.pa.us> (raw)
In-Reply-To: <3C6AC08C.0000A1.04458@ns.interchange.ca>
References: <3C6AC08C.0000A1.04458@ns.interchange.ca>

"Michael Richards" <michael@fastmail.ca> 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



view thread (9+ messages)

Message-ID: <883.1013637055@sss.pgh.pa.us>
Permalink:  ../883.1013637055@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/883.1013637055@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, michael@fastmail.ca, jason.earl@simplot.com
  Subject: Re: Time Intervals
  In-Reply-To: <883.1013637055@sss.pgh.pa.us>

* 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