Re: Rectifying wrong Date outputs - Mailing list pgsql-hackers
| From | Piyush Newe |
|---|---|
| Subject | Re: Rectifying wrong Date outputs |
| Date | |
| Msg-id | AANLkTimCej4qSjeat1hsh-3Bw+mWCXzQ8_1iqb3WuwLk@mail.gmail.com Whole thread Raw |
| In response to | Re: Rectifying wrong Date outputs (Bruce Momjian <bruce@momjian.us>) |
| Responses |
Re: Rectifying wrong Date outputs
|
| List | pgsql-hackers |
Sorry for creating the confusion. The table drawn was PostgreSQL vs EDB Advanced Server.
Thanks Burce for clarification.
For the 1-digit, 2-digit & 3-digit Year inputs, as I said, I didn't see any document in PG which will explain what would be the century considered if it is not given. If I missed out it somewhere please let me know.
I refer to following link which explains the behavior of Oracle.
Now, if
1. # of digits given is greater than the # of Ys
i.e.
postgres=# select to_date('01-jan-111', 'DD-MON-Y');
to_date
------------
2111-01-01
(1 row)
What we should do ? Either we should throw an error or we should give what user has provided.
IMHO, we should follow what format is given by user. However, even if the 'format' gets wrong rather invalid, it is not throwing any error.
e.g.
postgres=# select to_date('01-jan-111', 'DD-MON-Y POSTGRES'); <<<<---- Look at this
to_date
------------
2111-01-01
(1 row)
2. # of digits given is less than the # of Ys
Consider following case,
postgres=# select to_date('01-jan-6', 'DD-MON-Y'), to_date('01-jan-6', 'DD-MON-YYYY');
to_date | to_date
------------+------------
2006-01-01 | 0006-01-01
Why this behaviour not predictable ? I think we are always considering the current century, if it is not provided. If I missed out any related document, please share.
And yes,
postgres=# select to_date('01-jan-1761', 'DD-MON-Y');
to_date
------------
3761-01-01 <<<----- Look at this.
(1 row)
Definitely, their is a bug in this case.
Am I missing something ?
-Piyush
On Thu, Mar 17, 2011 at 3:30 AM, Bruce Momjian <bruce@momjian.us> wrote:
Oh, I understand now. I was confused that the headings didn't line upRobert Haas wrote:
> On Wed, Mar 16, 2011 at 5:52 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Robert Haas wrote:
> >> On Wed, Mar 16, 2011 at 8:21 AM, Piyush Newe
> >> <piyush.newe@enterprisedb.com> wrote:
> >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS
> >> > TO_DATE('01-jan-10', ?'DD-MON-Y') ?? ? ? ?2010-01-01 Error
> >> > TO_DATE('01-jan-10', ?'DD-MON-YY') ?? ? ? ?2010-01-01 01-JAN-2010
> >> > TO_DATE('01-jan-10', ?'DD-MON-YYY') 2010-01-01 01-JAN-2010
> >> > TO_DATE('01-jan-10', ?'DD-MON-YYYY') 0010-01-01 01-JAN-0010
> >> > In this case, it seems in last 3 cases PG is behaving correctly. Whereas in
> >> > 1st case the output is not correct since the Format ('Y') is lesser than the
> >> > actual input ('10'). But PG is ignoring this condition and throwing whatever
> >> > is input. The output year is might not be the year, what user is expecting.
> >> > Hence PG should throw an error.
> >>
> >> I can't get worked up about this. ?If there's a consensus that
> >> throwing an error here is better, fine, but on first blush the PG
> >> behavior doesn't look unreasonable to me.
> >>
> >> > Data Format ?? ? ? ? ? ? ? ?PostgreSQL EDBAS
> >
> > To clarify, the user is reporting EDB Advanced Server, though the
> > community PG has the same issues, or at least similar; ?with git HEAD:
> >
> > ? ? ? ?test=> SELECT TO_DATE('01-jan-2010', ?'DD-MON-YY');
> > ? ? ? ? ?to_date
> > ? ? ? ?------------
> > ? ? ? ? 3910-01-01
> > ? ? ? ?(1 row)
>
> Actually, I think he's comparing PostgreSQL to Advanced Server.
with the values. I see now the first value is community PG and the
second is EDBAS.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
--
Piyush S Newe
Principal Engineer
EnterpriseDB
office: +91 20 3058 9500
www.enterprisedb.com
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
pgsql-hackers by date: