Thread: Bug in to_timestamp()
============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Neil Bloomer Your email address : neilbloomer@coneyislandsystems.com System Configuration --------------------- Architecture (example: Intel Pentium) : Pentium II 266 Operating System (example: Linux 2.0.26 ELF) : Red Hat Linux 6.1 PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL 7.0.2 on i686-pc-linux-gnu, (Red Hat RPM) Compiler used (example: gcc 2.8.0) : compiled by gcc egcs-2.91.66 Please enter a FULL description of your problem: ------------------------------------------------ The to_timestamp function is not working as per the documentation. See the examples below. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- The following results were returned when the queries were executed through ipgsql, and similar results are returned through psql. select to_timestamp('20000816000001', 'YYYYMMDDHH24MISS') returns '30/12/1899' (wrong) select to_timestamp('2000 0816000001', 'YYYY MMDDHH24MISS') returns '16/08/2000 00:00:01' (ok) select to_timestamp('000816000001', 'YYMMDDHH24MISS') returns '16/08/0001' (wrong) If you know how this problem might be fixed, list the solution below: --------------------------------------------------------------------- ?
On Wed, 16 Aug 2000, Gqms2 Galway wrote: > > Please enter a FULL description of your problem: > ------------------------------------------------ > The to_timestamp function is not working as per the documentation. See the > examples below. > No. It is not bug. Where is in a documentation your example? Instead this, in the documentation is next: YYYY = year (4 or more digits) ^^^^^^^^^^^ Timestamp range is 4714 BC -- 1465001 AC. > select to_timestamp('20000816000001', 'YYYYMMDDHH24MISS') returns > '30/12/1899' (wrong) The PostgreSQL hasn't directly limited year. The to_timestamp() stop parse YYYY at first non-digit char. > select to_timestamp('2000 0816000001', 'YYYY MMDDHH24MISS') returns > '16/08/2000 00:00:01' (ok) Yes, it's right. If you want store full timestamp into one big number is better year keep to end of this number, like: test=# select to_timestamp('08160000012000', 'MMDDHH24MISSYYYY'); to_timestamp ------------------------2000-08-16 00:00:01+02 And YYY, YY, Y ... it's *hell*, and we support it because Oracle has it too. How number you want create from:'01' -- 'YY' ---> 2001, 1901 or 0001 .. grrrr to_timestamp() use last possibility. Some commets/suggestions about greater years than 9999 in to_timestamp() / to_date()? Thanks, Karel