| 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.
| 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.
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 9781906966041 | The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference | See the print edition |