DateStyle
DateStyle is a configuration parameter determining the display format for date and time values. It also determines the rules for interpreting ambiguous date input values.
DateStyle was added in PostgreSQL 7.3.
Default value
The default value for DateStyle is: ISO, MDY.
However initdb will create an entry in the generated postgresql.conf file corresponding to the behavior of the selected lc_time locale.
Usage
For historical reasons, DateStyle consists of two elements separated by a comma:
- the output format specification (
ISO,Postgres,SQL, orGerman) - the input/output specification for year/month/day ordering (
DMY,MDY, orYMD)
Following synonyms for the year/month/day ordering specification exist:
DMY:Euro,EuropeanMDY:US,NonEuro,NonEuropean
Each element can be set separately, e.g. SET DateStyle TO 'SQL' or SET DateStyle TO 'YMD'.
Change history
- PostgreSQL 7.3
- added (commit f0811a74)
Examples
DateStyle is one of a small number of GUCs whose canonical name is formatted in "CamelCase" style:
postgres=# SHOW datestyle; DateStyle ----------- ISO, YMD (1 row)
Controlling the interpretation of potentially ambiguous date strings:
postgres=# SET datestyle TO 'YMD';
SET
postgres=# SELECT '12-11-10'::date;
date
------------
2012-11-10
(1 row)
postgres=# SET datestyle TO 'MDY';
SET
postgres=# SELECT '12-11-10'::date;
date
------------
2010-12-11
(1 row)
Setting DateStyle to one of Postgres or SQL will generate output formatted in the counterintuitive, ambiguous style sadly still popular in the USA:
postgres=# SET datestyle TO 'Postgres, YMD';
SET
postgres=# SELECT '12-11-10'::date;
date
------------
11-10-2012
(1 row)
postgres=# SET datestyle TO 'SQL';
SET
postgres=# SELECT '12-11-10'::date;
date
------------
11/10/2012
(1 row)
The German output format uses periods as a delimiter:
postgres=# SET datestyle TO 'German, DMY';
SET
postgres=# SELECT '12-11-10'::date;
date
------------
12.11.2010
(1 row)
References
- PostgreSQL documentation: DateStyle
Useful links
- ISO 8601 - the classic XKCD public service announcement about date formatting
