pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16472: Bug in to_timestamp ?
4+ messages / 4 participants
[nested] [flat]
* BUG #16472: Bug in to_timestamp ?
@ 2020-06-01 10:32 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2020-06-01 10:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jesvh.cht@gmail.com
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 ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16472: Bug in to_timestamp ?
@ 2020-06-01 12:02 Alexander Korotkov <a.korotkov@postgrespro.ru>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander Korotkov @ 2020-06-01 12:02 UTC (permalink / raw)
To: jesvh.cht@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16472: Bug in to_timestamp ?
@ 2020-06-01 16:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexander Korotkov <a.korotkov@postgrespro.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-06-01 16:18 UTC (permalink / raw)
To: Alexander Korotkov <a.korotkov@postgrespro.ru>; +Cc: jesvh.cht@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16472: Bug in to_timestamp ?
@ 2020-06-02 01:19 鄭旭宏 <jesvh.cht@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: 鄭旭宏 @ 2020-06-02 01:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>; pgsql-bugs@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-06-02 01:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-01 10:32 BUG #16472: Bug in to_timestamp ? PG Bug reporting form <noreply@postgresql.org>
2020-06-01 12:02 ` Alexander Korotkov <a.korotkov@postgrespro.ru>
2020-06-01 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-02 01:19 ` 鄭旭宏 <jesvh.cht@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox