Обсуждение: 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.