Re: BUG #9265: why the same interval can't get the same timestamp?

On Tue, Feb 18, 2014 at 1:06 PM, <miaoyimin(at)huawei(dot)com> wrote:
> postgres=# select ('epoch'::pg_catalog.timestamptz + 1386201600 * '1
> second'::pg_catalog.interval);
...
> postgres=# select ('epoch'::pg_catalog.timestamptz + 16044 * '1
> day'::pg_catalog.interval);

Besides the previous explanations you should probably read

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

Particularly the paragraph which states: "Internally interval values
are stored as months, days, and seconds. This is done because the
number of days in a month varies, and a day can have 23 or 25 hours if
a daylight savings time adjustment is involved. The months and days
fields are integers while the seconds field can store fractions. " If
you play a bit with interval you ill notice seconds and minutes are
'folded' into hours for display:

cdrs=> select 1386201600 * '1 second'::pg_catalog.interval;
?column?
--------------
385056:00:00
(1 row)

but not into days, and days are not 'folded' into anything:

cdrs=> select 16044 * '1 day'::pg_catalog.interval;
?column?
------------
16044 days
(1 row)

and months are 'folded' into years:

cdrs=> select 160 * 12 * '1 month'::pg_catalog.interval;
?column?
-----------
160 years
(1 row)

Try making some queries like this and you'll posibly begin to
understand the problem:

cdrs=> select '111111 months 222222 days 3333333 seconds'::pg_catalog.interval;
interval
-----------------------------------------
9259 years 3 mons 222222 days 925:55:33
(1 row)

Francisco Olarte.