Обсуждение: BUG #16472: Bug in to_timestamp ?
The following bug has been logged on the website: Bug reference: 16472 Logged by: jesvh Email address: jesvh.cht@gmail.com PostgreSQL version: Unsupported/Unknown Operating system: windows 10 Description: Hi : I execute a update SQL as below : update Table1 set date1=to_timestamp('19790701000000', 'YYYYMMDDHH24MISS') where ..... date1 is a timestamp type column, the result in DB is 1979-07-01 01:00:00 where come from that '01' hour ? but if set to other date value, it works correct .... Is it a special bug ?
Hi! On Mon, Jun 1, 2020 at 1:38 PM PG Bug reporting form <noreply@postgresql.org> wrote: > Hi : > I execute a update SQL as below : > update Table1 set date1=to_timestamp('19790701000000', 'YYYYMMDDHH24MISS') > where ..... > > date1 is a timestamp type column, the result in DB is 1979-07-01 01:00:00 > where come from that '01' hour ? > > but if set to other date value, it works correct .... > Is it a special bug ? It's likely related to clock shift in your timezone. What is your timezone and PostgreSQL version? You can figure out them using following queries. show timezone; select version(); ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Alexander Korotkov <a.korotkov@postgrespro.ru> writes: > On Mon, Jun 1, 2020 at 1:38 PM PG Bug reporting form > <noreply@postgresql.org> wrote: >> I execute a update SQL as below : >> update Table1 set date1=to_timestamp('19790701000000', 'YYYYMMDDHH24MISS') >> where ..... >> date1 is a timestamp type column, the result in DB is 1979-07-01 01:00:00 >> where come from that '01' hour ? > It's likely related to clock shift in your timezone. July 1 would be an odd time for a seasonal DST shift ... but quickly thumbing through the tzdata files, I see one for Asia/Taipei on that date in 1979. So I guess that the OP is using that zone, and the answer to the question is "midnight of that date did not exist in Taiwan; their clocks shifted forward from 23:59:59 to 01:00:00". regards, tom lane
timezone = Asia/Taipei
version = PostgreSQL 9.6rc1, compiled by Visual C++ build 1800, 64-bit
Tom Lane <tgl@sss.pgh.pa.us> 於 2020年6月2日 週二 上午12:18寫道:
Alexander Korotkov <a.korotkov@postgrespro.ru> writes:
> On Mon, Jun 1, 2020 at 1:38 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> I execute a update SQL as below :
>> update Table1 set date1=to_timestamp('19790701000000', 'YYYYMMDDHH24MISS')
>> where .....
>> date1 is a timestamp type column, the result in DB is 1979-07-01 01:00:00
>> where come from that '01' hour ?
> It's likely related to clock shift in your timezone.
July 1 would be an odd time for a seasonal DST shift ... but quickly
thumbing through the tzdata files, I see one for Asia/Taipei on that date
in 1979. So I guess that the OP is using that zone, and the answer to the
question is "midnight of that date did not exist in Taiwan; their clocks
shifted forward from 23:59:59 to 01:00:00".
regards, tom lane