Thread: BUG #12326: I think maybe postgresql has a problem about timezone.

The following bug has been logged on the website:

Bug reference:      12326
Logged by:          张亚琪
Email address:      zhangyingyun001@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   CentOS
Description:

     hi all, I am a new. My postgresql's version is 8.4.18. And I have
encounter a problem about timezone.
     When I insert a Date value like 'Wed Dec 24 17:28:46 CST 2014' (it
comes from new Date() with Java), and then if you select this value , you
will find a big problem about time. However , If I insert this value without
the timezone of CST, the time is normal absolutely. So I haven't idea about
whether it is a bug. But I hope you can consider it.
      Thank you for your time. Thanks.

On 12/24/2014 1:54 AM, zhangyingyun001@gmail.com wrote:
>       hi all, I am a new. My postgresql's version is 8.4.18. And I have
> encounter a problem about timezone.
>       When I insert a Date value like 'Wed Dec 24 17:28:46 CST 2014' (it
> comes from new Date() with Java), and then if you select this value , you
> will find a big problem about time. However , If I insert this value without
> the timezone of CST, the time is normal absolutely. So I haven't idea about
> whether it is a bug. But I hope you can consider it.
>        Thank you for your time. Thanks.

CST is ambiguous, it can be either US Central Standard Time, or Chinese
Standard Time.

what SQL data type is the field you inserted this timestamp into, and
what result did you get back?  also, what is your local TIMEZONE
setting, as shown by the SQL command SHOW TIMEZONE;  ?

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Wed, Dec 24, 2014 at 3:15 AM, =E5=BC=A0=E4=BA=9A=E7=90=AA <zhangyingyun0=
01@gmail.com> wrote:

> hi John,
>
>           Thank you for your reply. I am in China.
>           You can look at my result as following:
>
> show timezone;
>
> TimeZone
>
> ----------
>
>  PRC
>
> (1 row)
>
>
>
>
> # update history_configuration set var_datetime =3D 'Wed Dec 24 18:52:46 =
CST
>> 2014' where var_name =3D 'lastHourAggr';
>
>
>
> # select * from history_configuration ;
>
>      var_name      | var_value |      var_datetime
>
> -------------------+-----------+------------------------
>
>  lastHourAggr      |           | 2014-12-25 08:52:46+08
>
>
> So I think this CST means Central Standard Time , right?  And then when I
> read data using select sql, it will show this time with postgresql timezo=
ne.
>
>
PostgreSQL does not remember what timezone the time was expressed as when
you inserted it.  Rather, it interprets the date as being expressed in the
indicated time zone, and translates it to universal time for storage.  (If
you didn't indicate a time zone in the string itself, then it interprets it
as being from the timezone the client has set to),  When you pull the date
back out, it translates it from universal time to whatever your local
timezone is set to, which in this case is PRC (aka +8).

Cheers,

Jeff

张亚琪 <zhangyingyun001@gmail.com> writes:
> show timezone;
> TimeZone
> ----------
>  PRC
> (1 row)

> # update history_configuration set var_datetime = 'Wed Dec 24 18:52:46 CST
>> 2014' where var_name = 'lastHourAggr';

> # select * from history_configuration ;
>      var_name      | var_value |      var_datetime
> -------------------+-----------+------------------------
>  lastHourAggr      |           | 2014-12-25 08:52:46+08

As John says, this behavior is perfectly expected because Postgres thinks
"CST" means US Central Standard Time (GMT-6, currently).

If you want CST to mean China Standard Time, you'll need to set up a
custom zone abbreviation file.  See
http://www.postgresql.org/docs/9.3/static/datetime-config-files.html

For some reason this interpretation of "CST" got missed out when preparing
the sample data in Asia.txt, but I think what you need is just

@INCLUDE Default
@OVERRIDE
CST  28800
        regards, tom lane

On 12/24/2014 7:11 AM, Tom Lane wrote:
>> # update history_configuration set var_datetime = 'Wed Dec 24 18:52:46 CST
>>> >>2014' where var_name = 'lastHourAggr';
>> ># select * from history_configuration ;
>> >      var_name      | var_value |      var_datetime
>> >-------------------+-----------+------------------------
>> >  lastHourAggr      |           | 2014-12-25 08:52:46+08
> As John says, this behavior is perfectly expected because Postgres thinks
> "CST" means US Central Standard Time (GMT-6, currently).
>
> If you want CST to mean China Standard Time, you'll need to set up a
> custom zone abbreviation file.  See
> http://www.postgresql.org/docs/9.3/static/datetime-config-files.html
>
> For some reason this interpretation of "CST" got missed out when preparing
> the sample data in Asia.txt, but I think what you need is just

we ran into this same problem in our java software (Java's Date object
returning CST for China), and we fixed it via using some option in Java
that output the time in an ISO format, like '2014-12-25 18:52:46+08',
which removes all ambiguity.   I'm not the Java programmer, so I don't
know what the exact code fix was.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast