pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Allan Engelhardt <allane@cybaea.com>
To: Josh Berkus <josh@agliodbs.com>
Subject: Re: Data type confusion
Date: Mon, 06 Aug 2001 21:13:23 +0100
Message-ID: <3B6EFA63.FA7326EB@cybaea.com> (raw)
References: <web-97333@davinci.ethosmedia.com>

Josh Berkus wrote:

> 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).
> Thus we don't want to hold up simple and obvious date multiplication and
> division just to deal with the wierdo cases.

I see now what you are trying to do.  It sort of makes sense, but I'm still really reluctant to give (semantic or otherwise) meaning to "yesterday divided by tomorrow" .....

Would it be a better solution if there was a conversion function a la

    convert(text, interval) RETURNS double precision

where text in ('day','week','hour', ....).  The function would convert the interval to the specified unit, with some considerable degree of fuzziness as discussed in this thread.

Then your query would be simply

    convert('week', '2 years 3 months') / 1

and would return something in the vicinity of 117.0  :-)


It seems to me that such a function would be more generally useful than the division of intervals.  What you really want to do is not to divide intervals, but to express them in different time units.  Or am I missing something (again)?


Comments?


Allan.




view thread (19+ messages)  latest in thread

Message-ID: <3B6EFA63.FA7326EB@cybaea.com>
Permalink:  ../3B6EFA63.FA7326EB@cybaea.com/
Also on:    postgresql.org/message-id/3B6EFA63.FA7326EB@cybaea.com

 · 

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: allane@cybaea.com, josh@agliodbs.com
  Subject: Re: Data type confusion
  In-Reply-To: <3B6EFA63.FA7326EB@cybaea.com>

* 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