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

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs