input date validation check calling to_date

Question

I'm using postgreSQL-9.1.6 on CENTOS.

I have issue on to_date function like...

postgres=# select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

so I'm trying to add validation check for out of range against incoming date.

postgres=# select to_date('20130229','yyyymmdd');

ERROR: timestamp out of range

I found a hint from here but it didn't work and I asked here. unfortunately, I couldn't get answer.

Finally, I reached another conclustion. Below is My formatting.c to which 9 lines marked + added.

Datum
to_date(PG_FUNCTION_ARGS)
{
        text       *date_txt = PG_GETARG_TEXT_P(0);
        text       *fmt = PG_GETARG_TEXT_P(1);
        DateADT         result;
        struct pg_tm tm;
        fsec_t          fsec;

    +   int ndays[]={-1,31,28,31,30,31,30,31,31,30,31,30,31};

    +   int last_day_of_month;

        do_to_timestamp(date_txt, fmt, &tm, &fsec);

    +   last_day_of_month = ndays[tm.tm_mon];
    +   if (((tm.tm_year & 3) == 0 && ((tm.tm_year % 25) != 0 || (tm.tm_year & 15) == 0)) && tm.tm_mon == 2 )
    +           last_day_of_month = ndays[tm.tm_mon] + 1;

    +   if( tm.tm_mon > 12 || tm.tm_mon < 1 || tm.tm_mday > last_day_of_month || tm.tm_mday < 1
)
    +           ereport(ERROR,
    +                           (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
    +                            errmsg("timestamp out of range")));

        result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;

        PG_RETURN_DATEADT(result);
}

although my own formatting.c works well, I'm not sure this works perfect. I'm worried about unexpected results like throwing error against the valid date.

Any advice would be very appreciated.

Answer 1

Must you do this by patching your server code? Especially when that patch will probably break currently working SQL and will need to be updated when you update PostgreSQL? This is also a bad idea because you'll get in the habit of expecting to_date to work the way your custom to_date works and things will go sideways when you work with an unpatched PostgreSQL. You might want to take into account other people that get stuck working with your custom PostgreSQL server too, how are they supposed to know that to_date really isn't to_date but some modified version? OTOH, perhaps you're doing this for job security.

Why not write your own to_date replacement function, called say strict_to_date, that does the conversion using to_date and then does a simple:

string = to_char(to_date_result, 'yyyymmdd')

comparison? If the to_date and to_char round trip doesn't give you your original result back then you can raise your exception. You'd need to decide what strict_to_date(null) would do of course but that's easy to add.

Consider some simple results:

=> select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

=> select to_char(to_date('20130229','yyyymmdd'), 'yyyymmdd');
 to_char  
----------
 20130301
(1 row)

Since '20130229' != '20130301' you have your exception. Wrap that in a function and slip in a little comment about why you're doing this and everyone should be happy.