Re: Bug in to_timestamp().

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Artur Zakirov <a.zakirov@postgrespro.ru>
To: Robert Haas <robertmhaas@gmail.com>
To: amul sul <sul_amul@yahoo.co.in>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Alex Ignatov <a.ignatov@postgrespro.ru>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Bug in to_timestamp().
Date: Tue, 16 Aug 2016 11:14:36 +0300
Message-ID: <d7ffcce8-491f-7969-71a6-73272ad31dfd@postgrespro.ru> (raw)
In-Reply-To: <CA+TgmoZEWyszzwDGFZ2i1psDgHrdPeZE5Hvs570Q1KfkCfqwgw@mail.gmail.com>
References: <1873520224.1784572.1465833145330.JavaMail.yahoo.ref@mail.yahoo.com>
	<f64563b3-5c3c-6f28-3f73-41b2159417bc@postgrespro.ru>
	<884791796.213185.1466667686999.JavaMail.yahoo@mail.yahoo.com>
	<20160623133001.GF21246@momjian.us>
	<1877e60a-a970-18b5-453c-e342f96150e6@postgrespro.ru>
	<CAKFQuwZKtS0Ad=a1E9_mkdYwLMq+JA3YjmhCvjJ1JF+3BU8a0g@mail.gmail.com>
	<d469b177-fd4d-5a5b-769e-620957ec1a64@postgrespro.ru>
	<CAKFQuwb2gp8n2ai_agyJod5BTYxL7ek0AjB9zifDSceK6QntnQ@mail.gmail.com>
	<CA+TgmoaWTgbtd2RTwVvySMVoOcHmWq66rCCxtVywOOX3FJy4tQ@mail.gmail.com>
	<31915.1466703655@sss.pgh.pa.us>
	<CA+TgmoaFQZeqmAo3CdivNu70coB=9eM5OroKYtNcTuMaC1kuUQ@mail.gmail.com>
	<32684.1466704957@sss.pgh.pa.us>
	<734e50cd-c563-4afd-d1c8-80f244b3265e@postgrespro.ru>
	<CAFj8pRDGxe=cdcZ7pur=2WeW9O3MQCZTBEAOJfQ7OLQJaYXDvw@mail.gmail.com>
	<b2a39359-3282-b402-f4a3-057aae500ee7@postgrespro.ru>
	<1285870380.13225431.1471272994354.JavaMail.yahoo@mail.yahoo.com>
	<CA+TgmoZEWyszzwDGFZ2i1psDgHrdPeZE5Hvs570Q1KfkCfqwgw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

On 15.08.2016 19:28, Robert Haas wrote:
>
> Well, what's the Oracle behavior in any of these cases?  I don't think
> we can decide to change any of this behavior without knowing that.  If
> a proposed behavior change is incompatible with our previous releases,
> I think it'd better at least be more compatible with Oracle.
> Otherwise, we're just changing from an established behavior that we
> invented ourselves to a new behavior we invented ourselves, which is
> only worthwhile if it's absolutely clear that the new behavior is way
> better.
>

1 - Oracle's output for first queries is:

-> SELECT TO_TIMESTAMP('2015-12-31 13:43:36', 'YYYY MM DD HH24 MI SS') 
FROM dual;

TO_TIMESTAMP('2015-12-3113:43:36','YYYYMMDDHH24MISS')
---------------------------------------------------------------------------
31-DEC-15 01.43.36.000000000 PM

-> SELECT TO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS') 
FROM dual;

TO_TIMESTAMP('2011$03!1823_38_15','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
18-MAR-11 11.38.15.000000000 PM

-> SELECT TO_TIMESTAMP('2011*03!18 #%23^38$15', 
'YYYY-MM-DD$$$HH24:MI:SS') FROM dual;

TO_TIMESTAMP('2011*03!18#%23^38$15','YYYY-MM-DD$$$HH24:MI:SS')
---------------------------------------------------------------------------
18-MAR-11 11.38.15.000000000 PM

PostgreSQL with the patch gives "ERROR:  expected space character in 
given string". I will fix this.


2 - Oracle's output for query with hyphen is:

-> SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD') FROM dual;
SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD') FROM dual
                     *
ERROR at line 1:
ORA-01843: not a valid month

Here PostgreSQL with the patch does not give an error. So I will fix 
this too.


3 - The last two queries give an error. This patch do not handle such 
queries intentionally, because there is the thread 
https://www.postgresql.org/message-id/57786490.9010201%40wars-nicht.de . 
That thread concerns to_date() function. But it should concerns 
to_timestamp() also. So I suppose that should be a different patch for 
this last case.

-- 
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

view thread (138+ messages)  latest in thread

Message-ID: <d7ffcce8-491f-7969-71a6-73272ad31dfd@postgrespro.ru>
Permalink:  ../d7ffcce8-491f-7969-71a6-73272ad31dfd@postgrespro.ru/
Also on:    postgresql.org/message-id/d7ffcce8-491f-7969-71a6-73272ad31dfd@postgrespro.ru

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: a.zakirov@postgrespro.ru, robertmhaas@gmail.com, sul_amul@yahoo.co.in, pavel.stehule@gmail.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, a.ignatov@postgrespro.ru, bruce@momjian.us
  Subject: Re: Bug in to_timestamp().
  In-Reply-To: <d7ffcce8-491f-7969-71a6-73272ad31dfd@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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