Postgres - default date format when interpreting strings

Question

What is the default data format that postgres uses when confronted with a character representation of a date?

These SQL statements all work, and the character string is always correctly interpreted as 11th of December 2013. In these examples there is no scope for mis-interpretation. (mydate is defined as timestamp)

SELECT * FROM mytable WHERE mydate = '11-Dec-2013';
SELECT * FROM mytable WHERE mydate = '11-2013-Dec';
SELECT * FROM mytable WHERE mydate = '2013-11-Dec';

These versions get interpreted as different values

SELECT * FROM mytable WHERE mydate = '11-12-13';
SELECT * FROM mytable WHERE mydate = '11-13-12';
SELECT * FROM mytable WHERE mydate = '13-11-12';

I assume there's a default format setting somewhere which postgres use to decide whether dates are read as "dd-mm-yy", "mm-dd-yy" or "yy-mm-dd" (or any other combination)

What is the default format and how do I change it?

I'm aware that I can explicitly define the format like this....

SELECT * FROM mytable WHERE mydate = TO_DATE('11-12-13','DD-MM-YY');

But implementing that would require a lot of code changes for me, and I'm aware of the SET datestyle command, but that appears to limit me to a set of explicit formats rather than allowing custom formats.

• user1822: In general you should never rely on any "default format". Always specify date or timestamp values using ANSI literals DATE '2013年12月11日'. Better bite the bullet and change the bad code.

Answer 1

There is the datestyle parameter which you can set to something like ISO, DMY or ISO, MDY to influence how such ambiguous dates are interpreted.

But if you allow free-form date input, PostgreSQL's heuristics are bound to get it wrong at some point. So to make your program robust, always force your dates into a certain format and either use the ISO format and rely on automatic conversion or use to_date to parse the strings.