Re: "interval hour to minute" or "interval day to minute"

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.
         */