DateStyle

A system parameter controlling the date/time display format

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, or German)
  • the input/output specification for year/month/day ordering (DMY, MDY, or YMD)

Following synonyms for the year/month/day ordering specification exist:

  • DMY: Euro, European
  • MDY: US, NonEuro, NonEuropean

Each element can be set separately, e.g. SET DateStyle TO 'SQL' or SET DateStyle TO 'YMD'.

Change history

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)
  • ISO 8601 - the classic XKCD public service announcement about date formatting

Categories

Date and time, GUC configuration item

See also

IntervalStyle, TimeZone