pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17305: to_timestamp accept dates rejected by timestamptz cast
3+ messages / 3 participants
[nested] [flat]
* BUG #17305: to_timestamp accept dates rejected by timestamptz cast
@ 2021-12-01 13:59 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-12-01 13:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: nguyenkhacchinh@hotmail.com
The following bug has been logged on the website:
Bug reference: 17305
Logged by: Chinh Nguyen
Email address: nguyenkhacchinh@hotmail.com
PostgreSQL version: 10.19
Operating system: Linux
Description:
I don't know if this is a bug or just an unusual behavior. I had assumed,
per the documentation, that function to_timestamp() is the same as
timestamptz but with control for different formats.
The following dates are all rejected by timestamptz (e.g., select
'0000-01-01T00:00:01Z'::timestamptz):
0000-01-01T00:00:01Z, 0000-02-29T00:00:01Z, 0000-02-30T00:00:01Z,
0000-02-31T00:00:01Z
But they are accepted by to_timestamp (e.g., select to_timestamp('0000-01-01
00:00:00+00','YYYY-MM-DDThh24:mi:ssZ')). The results from to_timestamp
are:
0001-01-01 00:00:00+00 BC, 0001-02-29 00:00:00+00 BC, 0001-03-01 00:00:00+00
BC, 0001-03-02 00:00:00+00 BC
My version is 10.19 but I also tested against an online test db running
11.10.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17305: to_timestamp accept dates rejected by timestamptz cast
@ 2021-12-02 18:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-12-02 18:54 UTC (permalink / raw)
To: nguyenkhacchinh@hotmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I don't know if this is a bug or just an unusual behavior. I had assumed,
> per the documentation, that function to_timestamp() is the same as
> timestamptz but with control for different formats.
It is not, and I don't see the documentation claiming that it is.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17305: to_timestamp accept dates rejected by timestamptz cast
@ 2021-12-03 15:06 Chinh Nguyen <nguyenkhacchinh@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Chinh Nguyen @ 2021-12-03 15:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I am simply going by this tip in the documentation:
"to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works, and is much easier. Similarly, to_number is unnecessary for standard numeric representations."
It's interesting that 0000-02-31 is acceptable for one but not the other. But given the unlikelihood of seeing this date in data that I process, this behaviour is simply a curiosity.
Regards.
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: December 2, 2021 1:54 PM
To: nguyenkhacchinh@hotmail.com <nguyenkhacchinh@hotmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17305: to_timestamp accept dates rejected by timestamptz cast
PG Bug reporting form <noreply@postgresql.org> writes:
> I don't know if this is a bug or just an unusual behavior. I had assumed,
> per the documentation, that function to_timestamp() is the same as
> timestamptz but with control for different formats.
It is not, and I don't see the documentation claiming that it is.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-12-03 15:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-01 13:59 BUG #17305: to_timestamp accept dates rejected by timestamptz cast PG Bug reporting form <noreply@postgresql.org>
2021-12-02 18:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-03 15:06 ` Chinh Nguyen <nguyenkhacchinh@hotmail.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