Forum OpenACS Q&A: to_date convertion in PostgreSQL

Collapse
1: to_date convertion in PostgreSQL
Posted by Andrei Popov on 06/13/01 01:07 PM

I've posted a patch a few weeks back which (I thought) fixes problem with to_date conversions used in /intranet/projects/ae-2.tcl. It appears it does not. The problem, I believe, is with the way Postgres handles date format. A sensible assumption is that when doing a conversion like to_date('2001-January-12'::varchar, 'YYYY-Month-DD'::varchar) should result in a date value of '2001-01-12' string.

Well, it does not. 'January' happens to be longer that 'Month' part in the mask. If you run this in psql you will probably get: 

acs=# select to_date('2001-January-12'::varchar, 'YYYY-Month-DD'::varchar);
  to_date
------------
 2001-01-01
(1 row)
which is not what you expected at all. Doing it like so: 
acs=# select to_date('2001-January-12'::varchar, 'YYYY-Mmmmmmm-DD'::varchar);
  to_date
------------
 2001-01-12
(1 row)
achieves the desired effect, but would you want to keep on modifying mask for months having varying name length??

This seems to me like an unfortunate Postgres mistreatment. This behavior does not correspond to the one described in documentation, that claims that 'Month' is

full mixed case month name (blank-padded to 9 chars)

So, what I had to do was change a few lines that compose start_date and end_date variables in /intranet/projects/ae-2.tcl to truncate names of the months to only 3 characters. Any other suggestions (I, for one, think that this should be fixed on Postgres side)?

Collapse
2: Response to to_date convertion in PostgreSQL (response to 1)
Posted by Vinod Kurup on 06/13/01 01:30 PM
I think the docs mean that postgres expects to be sent a 'month name' that has already been blank-padded to be 9 chars long.

So, if you pad the month before sending it to pg, it works: 

  openacs4=# select to_date('2001-January  -12'::varchar, 'YYYY-Month-DD'::varchar);    to_date  ------------   2001-01-12

Although, I agree that I would have expected pg to handle your version (and Oracle does).

Collapse
3: Response to to_date convertion in PostgreSQL (response to 1)
Posted by Don Baccus on 06/13/01 03:27 PM
Submit this as a bug to the PG development group ...