Re: Rectifying wrong Date outputs
Bruce Momjian <bruce@momjian.us>
From: Bruce Momjian <bruce@momjian.us>
To: Piyush Newe <piyush.newe@enterprisedb.com>
Cc: pgsql-hackers@postgresql.org
Date: 2011-09-07T13:51:04Z
Lists: pgsql-hackers
Applied, with a function rename. The only odd case we have left is:
test=> select to_date('079', 'YYY');
to_date
------------
1979-01-01
(1 row)
(Note the zero is ignored.) I can't see an easy way to fix this and
continue to be easily documented.
---------------------------------------------------------------------------
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Piyush Newe wrote:
> > > Hi,
> > >
> > > I was randomly testing some date related stuff on PG & observed that the
> > > outputs were wrong.
> > >
> > > e.g.
> > > postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY');
> > > to_date
> > > ------------
> > > 3910-01-01 <--------- Look at this
> > > (1 row)
> > >
> > > postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YYYY');
> > > to_date
> > > ------------
> > > 2010-01-01
> > > (1 row)
> >
> > I have done some work on this problem, and have developed the attached
> > patch. It genarates the output in the final column of this table:
> >
> > Oracle PostgreSQL With PG Patch
> > 1 TO_DATE('01-jan-1', 'DD-MON-Y') 01-JAN-2011 01-JAN-2001 01-JAN-2001+
> > 2 TO_DATE('01-jan-1', 'DD-MON-YY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
> > 3 TO_DATE('01-jan-1', 'DD-MON-YYY') 01-JAN-2001 01-JAN-2001 01-JAN-2001
> > 4 TO_DATE('01-jan-1', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
> > 5 TO_DATE('01-jan-10', 'DD-MON-Y') Error 01-JAN-2010 01-JAN-2010
> > 6 TO_DATE('01-jan-10', 'DD-MON-YY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
> > 7 TO_DATE('01-jan-10', 'DD-MON-YYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
> > 8 TO_DATE('01-jan-10', 'DD-MON-YYYY') 01-JAN-0010 01-JAN-0010 01-JAN-0010
> > 9 TO_DATE('01-jan-067', 'DD-MON-Y') Error 01-JAN-2067 01-JAN-2067
> > 10 TO_DATE('01-jan-111', 'DD-MON-YY') 01-JAN-0111 01-JAN-2011 01-JAN-2111*+
> > 11 TO_DATE('01-jan-678', 'DD-MON-YYY') 01-JAN-2678 01-JAN-1678 01-JAN-1678+
> > 12 TO_DATE('01-jan-001', 'DD-MON-YYYY') 01-JAN-0001 01-JAN-0001 01-JAN-0001
> > 13 TO_DATE('01-jan-2010', 'DD-MON-Y') Error 01-JAN-4010 01-JAN-2010*
> > 14 TO_DATE('01-jan-2010', 'DD-MON-YY') 01-JAN-2010 01-JAN-3910 01-JAN-2010*
> > 15 TO_DATE('01-jan-2010', 'DD-MON-YYY') Error 01-JAN-3010 01-JAN-2010*
> > 16 TO_DATE('01-jan-2010', 'DD-MON-YYYY') 01-JAN-2010 01-JAN-2010 01-JAN-2010
>
> In an attempt to make the to_date/to_timestamp behavior documentable, I
> have modified the patch to have dates adjust toward the year 2020, and
> added code so if four digits are supplied, we don't do any adjustment.
> Here is the current odd behavior, which is fixed by the patch:
>
> test=> select to_date('222', 'YYY');
> to_date
> ------------
> 2222-01-01
> (1 row)
>
> test=> select to_date('0222', 'YYY');
> to_date
> ------------
> 2222-01-01
> (1 row)
>
> If they supply a full 4-digit year, it seems we should honor that, even
> for YYY. YYYY still does no adjustment, and I doubt we want to change
> that:
>
> test=> select to_date('222', 'YYYY');
> to_date
> ------------
> 0222-01-01
> (1 row)
>
> test=> select to_date('0222', 'YYYY');
> to_date
> ------------
> 0222-01-01
> (1 row)
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
[ text/x-diff is unsupported, treating like TEXT/PLAIN ]
> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> new file mode 100644
> index c03dd6c..282bb0d
> *** a/doc/src/sgml/func.sgml
> --- b/doc/src/sgml/func.sgml
> *************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
> *** 5550,5555 ****
> --- 5550,5564 ----
>
> <listitem>
> <para>
> + If the year format specification is less than four digits, e.g.
> + <literal>YYY</>, and the supplied year is less than four digits,
> + the year will be adjusted to be nearest to year 2020, e.g.
> + <literal>95</> becomes 1995.
> + </para>
> + </listitem>
> +
> + <listitem>
> + <para>
> The <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
> <type>date</type> has a restriction when processing years with more than 4 digits. You must
> use some non-digit character or template after <literal>YYYY</literal>,
> diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
> new file mode 100644
> index 726a1f4..1a3ec1c
> *** a/src/backend/utils/adt/formatting.c
> --- b/src/backend/utils/adt/formatting.c
> *************** static void dump_node(FormatNode *node,
> *** 964,969 ****
> --- 964,970 ----
>
> static char *get_th(char *num, int type);
> static char *str_numth(char *dest, char *num, int type);
> + static int add_era_to_partial_year(int year);
> static int strspace_len(char *str);
> static int strdigits_len(char *str);
> static void from_char_set_mode(TmFromChar *tmfc, const FromCharDateMode mode);
> *************** is_next_separator(FormatNode *n)
> *** 1968,1973 ****
> --- 1969,1999 ----
> return TRUE; /* some non-digit input (separator) */
> }
>
> +
> + static int
> + add_era_to_partial_year(int year)
> + {
> + /*
> + * Adjust all dates toward 2020; this is effectively what happens
> + * when we assume '70' is 1970 and '69' is 2069.
> + */
> + /* Force 0-69 into the 2000's */
> + if (year < 70)
> + return year + 2000;
> + /* Force 70-99 into the 1900's */
> + else if (year >= 70 && year < 100)
> + return year + 1900;
> + /* Force 100-519 into the 2000's */
> + else if (year >= 100 && year < 519)
> + return year + 2000;
> + /* Force 520-999 into the 1000's */
> + else if (year >= 520 && year < 1000)
> + return year + 1000;
> + else
> + return year;
> + }
> +
> +
> static int
> strspace_len(char *str)
> {
> *************** DCH_from_char(FormatNode *node, char *in
> *** 2930,2972 ****
> break;
> case DCH_YYY:
> case DCH_IYY:
> ! from_char_parse_int(&out->year, &s, n);
> out->yysz = 3;
> -
> - /*
> - * 3-digit year: '100' ... '999' = 1100 ... 1999 '000' ...
> - * '099' = 2000 ... 2099
> - */
> - if (out->year >= 100)
> - out->year += 1000;
> - else
> - out->year += 2000;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_YY:
> case DCH_IY:
> ! from_char_parse_int(&out->year, &s, n);
> out->yysz = 2;
> -
> - /*
> - * 2-digit year: '00' ... '69' = 2000 ... 2069 '70' ... '99'
> - * = 1970 ... 1999
> - */
> - if (out->year < 70)
> - out->year += 2000;
> - else
> - out->year += 1900;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_Y:
> case DCH_I:
> ! from_char_parse_int(&out->year, &s, n);
> out->yysz = 1;
> -
> - /*
> - * 1-digit year: always +2000
> - */
> - out->year += 2000;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_RM:
> --- 2956,2978 ----
> break;
> case DCH_YYY:
> case DCH_IYY:
> ! if (from_char_parse_int(&out->year, &s, n) < 4)
> ! out->year = add_era_to_partial_year(out->year);
> out->yysz = 3;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_YY:
> case DCH_IY:
> ! if (from_char_parse_int(&out->year, &s, n) < 4)
> ! out->year = add_era_to_partial_year(out->year);
> out->yysz = 2;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_Y:
> case DCH_I:
> ! if (from_char_parse_int(&out->year, &s, n) < 4)
> ! out->year = add_era_to_partial_year(out->year);
> out->yysz = 1;
> s += SKIP_THth(n->suffix);
> break;
> case DCH_RM:
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +