Postgresql – Postgres – default date format when interpreting strings

date formatpostgresqlpostgresql-10

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.

Best Answer

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.

Related Question