pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: Data type confusion
Date: Mon, 06 Aug 2001 11:33:36 -0400
Message-ID: <21804.997112016@sss.pgh.pa.us> (raw)
In-Reply-To: <web-97333@davinci.ethosmedia.com>
References: <web-97333@davinci.ethosmedia.com>

"Josh Berkus" <josh@agliodbs.com> writes:
> Peter is absolutely correct here.  '1 year'::INTERVAL - '1
> day'::INTERVAL is '364 days'::INTERVAL most of the time.  However, on
> leap years it is '365 days'.

Au contraire, it is always '1 year - 1 day'::INTERVAL.  That is a
two-part interval value and is not reduced further.  When you add it
to a date or timestamp, *then* you find out how many days are meant.

> One way to simplify this would be not to allow any division operations
> on INTERVALS that result in a modulo of a smaller increment than the
> INTERVAL value expressed.  Thus, one could "'3 months'::INTERVAL / 3"
> but would not be allowed to "'2 months::INTERVAL / 3".   However, this
> seems kind of unfair to hour, minute, and second values whose fractions
> are well-defined and easily manipulated.

I was toying with the notion of allowing scalings whose results didn't
introduce any fractional part to the "months" field.  For example

'2 months + 1 day' / 2.0  =  '1 month + 12hrs'

'3 months + 1 day' / 2.0  =  error (can't have a half month)

'61 days' / 2.0		  =  '30 days 12hrs'

However, I fear that this would make no sense to anyone who hadn't
thought about the issues as carefully as we have in this thread.

> Or, to put it another way, 95% of the time users just want to do simple
> things.  Like we want to know how many weeks an employee has been with
> us for:  '2 years 3 months'::INTERVAL / '1 week'::INTERVAL  (and we
> don't care about the fractional week left over).

Good point.  Ugly as the "30 day" convention is, it is probably close
enough for that sort of thing.

			regards, tom lane



view thread (19+ messages)  latest in thread

Message-ID: <21804.997112016@sss.pgh.pa.us>
Permalink:  ../21804.997112016@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/21804.997112016@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, josh@agliodbs.com, peter_e@gmx.net
  Subject: Re: Re: Data type confusion
  In-Reply-To: <21804.997112016@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