Thread: "interval hour to minute" or "interval day to minute"
Hi
I discovered the 'fields' option of 'interval', but i can't figure out
from the docs how it is supposed to work. Are "hour to minute" and "day
to minute" really the same thing? And if not, in what circumstances are
they treated differently?
psql (8.4.7)
Type "help" for help.
postgres=> select '3 years 2 months - 1 day + 10 hours 5
minutes'::interval day to minute;
interval
----------------------------------
3 years 2 mons -1 days +10:05:00
(1 row)
postgres=> select '3 years 2 months - 1 day + 10 hours 5
minutes'::interval hour to minute;
interval
----------------------------------
3 years 2 mons -1 days +10:05:00
(1 row)
Warm regards
Jack Douglas
On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote:
> I discovered the 'fields' option of 'interval', but i can't figure out
> from the docs how it is supposed to work. Are "hour to minute" and "day
> to minute" really the same thing? And if not, in what circumstances are
> they treated differently?
As of version 8.4, they behave identically. The code has this comment, some
form of which probably belongs in the documentation:
/*
* Our interpretation of intervals with a limited set of fields is
* that fields to the right of the last one specified are zeroed out,
* but those to the left of it remain valid. Thus for example there
* is no operational difference between INTERVAL YEAR TO MONTH and
* INTERVAL MONTH. In some cases we could meaningfully enforce that
* higher-order fields are zero; for example INTERVAL DAY could reject
* nonzero "month" field. However that seems a bit pointless when we
* can't do it consistently. (We cannot enforce a range limit on the
* highest expected field, since we do not have any equivalent of
* SQL's <interval leading field precision>.)
*
* Note: before PG 8.4 we interpreted a limited set of fields as
* actually causing a "modulo" operation on a given value, potentially
* losing high-order as well as low-order information. But there is
* no support for such behavior in the standard, and it seems fairly
* undesirable on data consistency grounds anyway. Now we only
* perform truncation or rounding of low-order fields.
*/
Noah Misch wrote: > On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: > > I discovered the 'fields' option of 'interval', but i can't figure out > > from the docs how it is supposed to work. Are "hour to minute" and "day > > to minute" really the same thing? And if not, in what circumstances are > > they treated differently? > > As of version 8.4, they behave identically. The code has this comment, some > form of which probably belongs in the documentation: > > /* > * Our interpretation of intervals with a limited set of fields is > * that fields to the right of the last one specified are zeroed out, > * but those to the left of it remain valid. Thus for example there > * is no operational difference between INTERVAL YEAR TO MONTH and > * INTERVAL MONTH. In some cases we could meaningfully enforce that > * higher-order fields are zero; for example INTERVAL DAY could reject > * nonzero "month" field. However that seems a bit pointless when we > * can't do it consistently. (We cannot enforce a range limit on the > * highest expected field, since we do not have any equivalent of > * SQL's <interval leading field precision>.) > * > * Note: before PG 8.4 we interpreted a limited set of fields as > * actually causing a "modulo" operation on a given value, potentially > * losing high-order as well as low-order information. But there is > * no support for such behavior in the standard, and it seems fairly > * undesirable on data consistency grounds anyway. Now we only > * perform truncation or rounding of low-order fields. > */ I am lost on how we could mention that in the docs. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, Jun 16, 2011 at 06:07:50PM -0400, Bruce Momjian wrote: > Noah Misch wrote: > > On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: > > > I discovered the 'fields' option of 'interval', but i can't figure out > > > from the docs how it is supposed to work. Are "hour to minute" and "day > > > to minute" really the same thing? And if not, in what circumstances are > > > they treated differently? > > > > As of version 8.4, they behave identically. The code has this comment, some > > form of which probably belongs in the documentation: > > > > /* > > * Our interpretation of intervals with a limited set of fields is > > * that fields to the right of the last one specified are zeroed out, > > * but those to the left of it remain valid. Thus for example there > > * is no operational difference between INTERVAL YEAR TO MONTH and > > * INTERVAL MONTH. In some cases we could meaningfully enforce that > > * higher-order fields are zero; for example INTERVAL DAY could reject > > * nonzero "month" field. However that seems a bit pointless when we > > * can't do it consistently. (We cannot enforce a range limit on the > > * highest expected field, since we do not have any equivalent of > > * SQL's <interval leading field precision>.) > > * > > * Note: before PG 8.4 we interpreted a limited set of fields as > > * actually causing a "modulo" operation on a given value, potentially > > * losing high-order as well as low-order information. But there is > > * no support for such behavior in the standard, and it seems fairly > > * undesirable on data consistency grounds anyway. Now we only > > * perform truncation or rounding of low-order fields. > > */ > > I am lost on how we could mention that in the docs. Perhaps something like this?