Hi,
We have some legacy tables with timestamps in an unusual String format - year, month, day, hour, minute, second, millisecond with no separators.
In the docs it appears the 'MS' and 'FF3' date time template patterns are identical but the behaviour is different. We also support Oracle which doesn't have 'MS', so we tried to use the pattern 'YYYYMMDDHH24MISSFF3'. This doesn't work, but switching to 'MS' does.
-------------------------------------------------------------------------------------
psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.
postgres=# select to_timestamp('20241206111433123', 'YYYYMMDDHH24MISSFF3');
ERROR: date/time field value out of range: "20241206111433123"
postgres=# select to_timestamp('20241206111433123', 'YYYYMMDDHH24MISSMS');
to_timestamp
----------------------------
2024-12-06 11:14:33.123+00
(1 row)
-------------------------------------------------------------------------------------
Other observations:
I'd expect these patterns to work the same way - or at least to be documented differently.
FF3 works fine if there's a separator in the string, i.e. select to_timestamp('20241206111433.123', 'YYYYMMDDHH24MISS.FF3');
The other FF<n> patterns behave the same way.
I've reproduced this on Postgres 17.2 on Linux/x86_64 and Linux/ARM64, and also Postgres 13.18 on Linux/ARM64
Thanks very much,
Nick Davies
This message is private and confidential. It must not be distributed without our consent. If you have received this message in error, please notify us immediately, remove it from your system and do not disclose, distribute or retain this e-mail or any part of it. Unless expressly stated, opinions in this e-mail are those of the individual author and not those of Iliad Solutions , its directors or officers or any of its subsidiaries. You must take full responsibility for virus checking this e-mail and any attachments. Please note that the content of this e-mail or any of its attachments may also contain personal data that falls within the scope of the Data Protection Act 2018 and the General Data Protection Regulation. You must ensure that any handling or processing of such data by you is fully compliant with these legal and regulatory requirements.
