Thibauld - Imagination and Execution -

postgresql, date input format and black magic

I hate when I have the impression that black magic took the control of my PC. If you're a developer, you know it happens sometimes... when something is obviously wrong but you just can't figure out *what* is wrong. Hopefully, we work in IT, a field where we can reasonably assume that *everything* happens for a reason. So after digging a while, you eventually end up finding where the issue comes from.

It happened this week while trying to figure out why Postgresql had a different behavior on apparently completely similar platforms. I'm born April 1st, let's say I want to save my birthday in a table, I'd go with the following SQL statement :
INSERT INTO birthdays (date) VALUES ('01-04-1981');

As I'm french, each time I deal with dates, I tend to verify that they were understood correctly. Indeed, the above date format was french ('dd-mm-YYYY') but postgresql could have interpreted as a US date format ('mm-dd-YYY'). So let's check with the following statement:
SELECT date_part('month',date) FROM birthdays;

Here comes the problem. On 2 different machines with (apparently) the same setup, this statement would return 2 different results. One would return '04' (correct) and the other one would return '01' (wrong).

I took me a while to find out that postgresql had datestyle option (in postgresql.conf) which defines the rule to interpret ambiguous date input values.

So if datestyle is set to 'iso, dmy', it means dates will be interpreted as 'date-month-year' and if it is set to 'iso-mdy', dates will be interpreted as 'month-date-year'. It is that simple... once you found it