Horton

Thread

  1. Bug in to_timestamp()

    Gqms2 Galway <gqms2@nortelnetworks.com> — 2000-08-16T21:41:18Z

    ============================================================================
                        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:
---------------------------------------------------------------------
?

  2. Re: Bug in to_timestamp()

    Karel Zak <zakkr@zf.jcu.cz> — 2000-08-17T10:47:43Z

    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