Обсуждение: BUG #9265: why the same interval can't get the same timestamp?

The following bug has been logged on the website:

Bug reference:      9265
Logged by:          yimin
Email address:      miaoyimin@huawei.com
PostgreSQL version: 9.2.6
Operating system:   suse 10.3
Description:

postgres=# select ('epoch'::pg_catalog.timestamptz + 1386201600 * '1
second'::pg_catalog.interval);
        ?column?
------------------------
 2013-12-05 08:00:00+08
(1 row)

postgres=# select ('epoch'::pg_catalog.timestamptz + 16044 * '1
day'::pg_catalog.interval);
        ?column?
------------------------
 2013-12-05 07:30:00+08
(1 row)

postgres=# show timezone;
     TimeZone
-------------------
 Asia/Kuala_Lumpur
(1 row)

postgres=# select * from version();
                                             version
--------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.6 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20070115
(SUSE Linux), 32-bit
(1 row)

On 02/18/2014 01:06 PM, miaoyimin@huawei.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      9265
> Logged by:          yimin
> Email address:      miaoyimin@huawei.com
> PostgreSQL version: 9.2.6
> Operating system:   suse 10.3
> Description:
>
> postgres=# select ('epoch'::pg_catalog.timestamptz + 1386201600 * '1
> second'::pg_catalog.interval);
>         ?column?
> ------------------------
>  2013-12-05 08:00:00+08
> (1 row)
>
> postgres=# select ('epoch'::pg_catalog.timestamptz + 16044 * '1
> day'::pg_catalog.interval);
>         ?column?
> ------------------------
>  2013-12-05 07:30:00+08
> (1 row)
>
> postgres=# show timezone;
>      TimeZone
> -------------------
>  Asia/Kuala_Lumpur
> (1 row)
>
>


The timezone changed by thirty minutes on December 31, 1981 so the
number of days since epoch is not the same as the number of seconds.

--
Vik

miaoyimin@huawei.com writes:
> postgres=# select ('epoch'::pg_catalog.timestamptz + 1386201600 * '1
> second'::pg_catalog.interval);
>         ?column?
> ------------------------
>  2013-12-05 08:00:00+08
> (1 row)

> postgres=# select ('epoch'::pg_catalog.timestamptz + 16044 * '1
> day'::pg_catalog.interval);
>         ?column?
> ------------------------
>  2013-12-05 07:30:00+08
> (1 row)

It's intentional that those don't give the same result.  Adding days
is DST-aware, adding seconds is not.  Since the epoch in that zone
was

# select 'epoch'::pg_catalog.timestamptz;
        timestamptz
---------------------------
 1970-01-01 07:30:00+07:30
(1 row)

adding any number of days to it will produce 07:30 local time on
the selected day, even though the zone offset changes.

            regards, tom lane

On Tue, Feb 18, 2014 at 1:06 PM,  <miaoyimin@huawei.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.