BUG #16472: Bug in to_timestamp ?

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寫道：