0

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.

  • 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. – a_horse_with_no_name Feb 27 at 9:35
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.