pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Joseph Barillari <jbarilla@princeton.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Casting dates
Date: Mon, 29 Apr 2002 09:02:07 -0700
Message-ID: <web-1387712@davinci.ethosmedia.com> (raw)
In-Reply-To: <m3helvmifc.fsf@washer.barillari.org>
Joe,
> Does anyone know if it's possible to calculate the number of days (or
> weeks, seconds, minutes, or what-have-you) in a given interval
> without
> casting to EPOCH and performing manipulations there?
>
> PostgreSQL usually returns intervals created by subtraction in days
> and smaller fragments, I would like to know if it's possible for it
> to
> return years. (Integer division of the number of days by 365 would
> produce an almost-correct result, but it's rather inelegant.)
Unfortunately, you've hit one of Postgres implementation limits.
Multiplying and dividing INTERVAL values, while covered by the SQL92
spec, has not been completely implemented in Postgres. That is, it
should be possible for you to:
SELECT INTERVAL '3 days 5 hours' / INTERVAL '1 minute'
However, implementing this has been a challenge for all SQL-DB
designers. I don't know of any DB that does handle INTERVAL division.
It's a surmountable challenge, though, but requires somebody to take
the lead in a) working out the logic, and b) writing the code to
implement it. A) is not a trivial task, either ... while 60 minutes /
1 second is obvious, how about 4 months / 4 days? Months are not
constant in length.
I've had some ideas for a spec for this myself (expanding on the rather
terse spec in SQL 92) but have not had time to write it up. Good luck.
-Josh Berkus
view thread (3+ messages) latest in thread
Message-ID: <web-1387712@davinci.ethosmedia.com> Permalink: ../web-1387712@davinci.ethosmedia.com/ Also on: postgresql.org/message-id/web-1387712@davinci.ethosmedia.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: josh@agliodbs.com, jbarilla@princeton.edu Subject: Re: Casting dates In-Reply-To: <web-1387712@davinci.ethosmedia.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