Skip site navigation (1) Skip section navigation (2)

Calculating repeating events - functionality lost with the demise of tinterval ?

From: Netzach <psql-novice(at)netzach(dot)co(dot)il>
To: pgsql-novice(at)postgresql(dot)org
Subject: Calculating repeating events - functionality lost with the demise of tinterval ?
Date: 2008-07-02 10:21:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-8-I"
<body alink="#ee0000" bgcolor="#ffffff" link="#0000ee" text="#33ff33"
<font color="#000000"><tt>Hi list,<br>
I need to know whether a repeating event occurs or not on a particular
day. I currently have the following information:<br>
date of first occurrence (date)<br>
repeat frequency (interval)<br>
date to check (date)<br>
My instinct says that I should be using a mod here to calculate whether
the remainder is zero when the difference between the two times is
divided by the repeat frequency. Unfortunately this is not possible
since as far as I am aware intervals only specify a time length and not
two dates. When searching I discovered tinterval which appears to do
what I would want, and was surprised to discover that it is not
supported in my postgres install because it is too _new_ !?! (PG 8.1).<br>
I then considered trying to extract the units from the repeat_frequency
interval and then calculating the difference between date_to_check and
first_occurrence in those units (eg weeks, months). But I was unable to
find a reasonable way to find out the units of an interval.<br>
I could try an approximation using </tt><tt class="LITERAL"><code
 class="FUNCTION">justify_interval() and friends and then use a short
loop to get an accurate result. This is also a hack.</code></tt><br>
This has to be a very standard requirement. How does one go about
calculating whether a difference between two dates is a multiple of a
particular interval in an elegant fashion ?<br>

Attachment: unknown_filename
Description: text/html (1.7 KB)


pgsql-novice by date

Next:From: Mike EllsworthDate: 2008-07-02 13:40:37
Subject: Re: Adding to a date
Previous:From: Yos SunitiyosoDate: 2008-07-02 09:42:48
Subject: Re: Automatic SQL command execution

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group