Re: to_date problems (Re: Favor for Postgres User at WSI) - Mailing list pgsql-bugs

Tom and Karel,

Thank you for your responses.

Based on your email, I have worked out a solution.

The reason I am using the to_date function is because I have two data bases into
which I am inserting, one is postgres, the other Oracle.  So I need a syntax
solution which will work with both.

Since I am actually using perl to connect to the data bases, I could modify the code
to parse the date string before it creates the insert statement.  I have done this
with other syntax differences, but I am trying to keep this to a minimum.

Another issue is that my input date string is coming from an external source.  It is
part of a message which I am parsing, and putting in the data base.  The format of
the date string is YYMMDD.

So, I have created a function:

pbi=# create function my_to_date( text, text) returns timestamp
pbi-# as 'select $1::timestamp'
pbi-# language 'sql';

which when run, returns:

pbi=# select my_to_date( '001112', 'yymmdd');
       my_to_date
------------------------
 2000-11-12 00:00:00+00
(1 row)

I made my function look like the the real one, although it does not use the second
argument.  I may modify this to use the second argument and/or to add punctuation (
/, :, -) to the time string to make it less ambiguous.

Any way, thank you both again for your help.

Cheers,
Kate

Karel Zak wrote:

> On Tue, 7 Nov 2000, Tom Lane wrote:
>
> > Kate Collins <klcollins@wsicorp.com> writes:
> > >> In other words it is defaulting to the year 0 (actually year 1 BC, since
> > >> there is no year 0) instead of 2000.
> >
> > Hmm, you're right:
> >
> > regression=# select to_date( '001112', 'YYMMDD');
> >     to_date
> > ---------------
> >  0001-11-12 BC
> > (1 row)
> >
> > >> Now I run the equivalent select statement on Oracle, and I get:
> > >> select to_char( to_date( '001112', 'YYMMDD'), 'YYYYMMDD') from dual;
> > >> TO_CHAR(TO_DATE('001112','YYMMDD'),'YYYYMMDD')
> > >> ---------------------------------------------------------------------------
> > >> 20001112
> > >>
> > >> Which is what I expect.
> > >>
> > >> Is "YY" suppose to default to the current century or is this an
> > >> Oracle'ism?
> >
> > I dunno whether there is any actual spec for to_date(), but I do agree
> > that if you've specified a 2-digit YY format, something 2000-centric
> > would be more useful than the current behavior.
> >
> > It doesn't seem to be doing anything particularly sensible with a
> > 4-digit date, either:
> >
> > regression=# select to_date( '00001112', 'YYYYMMDD');
> >   to_date
> > ------------
> >  1112-11-12
> > (1 row)
> >
> > This case I *would* have expected to produce 1 BC, but nope...
>
>  Where is *guarantee* that the year is 4-digits?!
>
> test=# select '123456-11-12'::date;
>    ?column?
> --------------
>  123456-11-12
> (1 row)
>
> The to_char/timestamp not try expect that YYYY = 4-digits (see docs)
>
> test=# select to_char('123456-11-12'::timestamp, 'YYYY DD/Mon');
>     to_char
> ---------------
>  123456 12/Nov
> (1 row)
>
> I mean that use in to_date() year in the first position is not good
> idea, better is try:
>
> test=# select to_date('11122000', 'MMDDYYYY');
>   to_date
> ------------
>  2000-11-12
>
> And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
>
> >
> > >> BTW, on postgres, when I try:
> > >> select to_char( to_date( '20001112', 'YYYYMMDD'), 'YYYYMMDD');
> > >> I get the error:  "ERROR:  Unable to convert timestamp to date"
> >
> > That seems broken in current sources, too:
> >
> > regression=# select to_date( '20001112', 'YYYYMMDD');
> > ERROR:  Unable to convert timestamp to date
>
> Yes, because to_date() expect that year is '20001112' and internal
> PG's date/time routines disable convert it.
>
> test=# select to_date( '2000-1112', 'YYYY-MMDD');
>   to_date
> ------------
>  2000-11-12
>
> > Looks like you've rooted out a number of problems in to_date (which
> > in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
> > whom I hope will find a fix for 7.1.
>
>  How? Create limit for YYYY to 4-digits?
>
> > BTW, direct conversion to timestamp does something sensible in all
> > these cases:
> >
> > regression=# select  '001112'::timestamp;
> >         ?column?
> > ------------------------
> >  2000-11-12 00:00:00-05
> > (1 row)
>
> Why not 1900?
>
> test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
>         ?column?
> ------------------------
>  2000-12-11 00:00:00+01
> (1 row)
>
> What is right here? Really '00' = 2000? .... but input is 1900
>
> > regression=# select  '00001112'::timestamp;
> >    ?column?
> > ---------------
> >  0001-11-12 BC
> > (1 row)
> >
> > regression=# select  '20001112'::timestamp;
> >         ?column?
> > ------------------------
> >  2000-11-12 00:00:00-05
> > (1 row)
>
> Well, thy use this logic for greater year:
>
>  test=# select  '20000-11-12'::timestamp;
>       ?column?
> ---------------------
>  20000-11-12 00:0000
> (1 row)
>
> test=# select  '200001112'::timestamp;
> ERROR:  Bad timestamp external representation '200001112'
> test=#
>
> ???
>
>  Well, possible solution: to_timestamp/date see if in the input is
> some separator (non-digit char) between YYYY and next template (like DD),
> if separator not here to_date() will expect 4-digits year.
>
>  '20001112'     not separator --> 4-digits for year = 2000
>  '20000-11-12'  separator '-' --> read it as 20000
>
>  '200001112'    not separator --> 4-digits for year = 2000
>                               --> 2-digits for month = 01
>                               --> 2-digits for day = 11
>                               --> last '2' is ignored
>
>  With 'YY' *hell* I not sure... add current year IMHO not must be
> correct. I mean that correct solution is:
>
> test=# select to_date('00-12-11', 'YY-DD-MM');
> ERROR:  Can't convert 'YY' to a real year.
>
> But if users want for their Oracle->PG port use 'YY' as last two digits
> in the current year, not problem make it....
>
>                                 Karel

--
=================================================
Katherine (Kate) L. Collins
Senior Software Engineer/Meteorologist
Weather Services International (WSI Corporation)
4 Federal Street
Billerica, MA 01821
EMAIL:  klcollins@wsicorp.com
PHONE:  (978) 670-5110
FAX:    (978) 670-5100
http://www.intellicast.com