- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

6.5.2 Date/Time Output

The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the “SQL” output format is a historical accident.) Table 6-14 shows examples of each output style. The output of the date and time types is of course only the date or time part in accordance with the given examples.

Table 6-14: Date/Time Output Styles
Style Specification Description
ISO ISO 8601/SQL standard
e.g. 1997-12-17 07:37:16-08
SQL traditional style
e.g. 12/17/1997 07:37:16.00 PST
POSTGRES original style
e.g. Wed Dec 17 07:37:16 1997 PST
German regional style
e.g. 17.12.1997 07:37:16.00 PST

In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears before day. (See section 6.5.1 Date/Time Input for how this setting also affects interpretation of input values.) Table 6-15 shows an example.

Table 6-15: Date Order Conventions
datestyle Setting Input Ordering Example Output
SQL, DMY day/month/year 17/12/1997 15:37:16.00 CET
SQL, MDY month/day/year 12/17/1997 07:37:16.00 PST
Postgres, DMY day/month/year Wed 17 Dec 07:37:16 1997 PST

The date/time styles can be selected by the user using the SET datestyle command, the DateStyle parameter in the ‘postgresql.conf’ configuration file, or the PGDATESTYLE environment variable on the server or client. The formatting function to_char (see section 7.8 Data Type Formatting Functions) is also available as a more flexible way to format date/time output.

ISBN 9781906966041The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language ReferenceSee the print edition