This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Date input can be accepted by PostgreSQL in many common formats, including the ISO-8601 format, the traditional SQL format, the original PostgreSQL format, and more. Table 3-15 lists several of these date formats.
These formats are relevant to the date and the timestamp data types.
Table 3-15. Valid Date Formats
Format Example | Description |
---|---|
July 1, 2001 | Format inspecific: named month, day and year |
Sunday July 1, 2001 | Format inspecific: named day, named month, day and year |
July 15, 01 BC | Format inspecific: named month, day and year before the Common Era |
2001-07-01 | Standard ISO-8601 format: numeric year, month and day |
20010715 | ISO-8601: formatted numerically as complete year, month, day |
010715 | ISO-8601: formatted numerically as 2-digit year, month, day |
7/01/2001 | Non-European (US) format: numeric month, day and year |
1/7/2001 | European format: numeric day, month and year |
2001.182 | Numeric format, with complete year, and sequential day of the year |
When specifying a named month in a date value to PostgreSQL, you may either type the complete month name, or choose from a set of defined abbreviations for each month. These abbreviations are listed in Table 3-16 .
Table 3-16. Month Abbreviations
Month | Abbreviation |
---|---|
January | Jan |
February | Feb |
March | Mar |
April | Apr |
May | May |
June | Jun |
July | Jul |
August | Aug |
September | Sep, Sept |
October | Oct |
November | Nov |
December | Dec |
Similarly, Table 3-17 lists PostgreSQL-recognized abbreviations for weekday names.
Table 3-17. Day of the Week Abbreviations
Day | Abbreviation |
---|---|
Sunday | Sun |
Monday | Mon |
Tuesday | Tue, Tues |
Wednesday | Wed, Weds |
Thursday | Thu, Thur, Thurs |
Friday | Fri |
Saturday | Sat |
Despite the wide variety of ways in which PostgreSQL can interpret date values, the values are always stored uniformally, and will be returned in a consistent format. As such, you have a variety of methods available to you to customize the default behavior with which date and time values are returned to you.
![]() | On Formatting |
---|---|
While date values can always be formatted after being selected via several formatting functions (e.g., to_char() ), it is more efficient to configure your defaults as close to the most commonly used conventions as you can before having to resort to manual type conversion and text formatting. |
To set the general date/time output format, the SET command can be applied to the run-time variable DATESTYLE . This variable may be set to one of four available general styles shown in Table 3-18 .
Table 3-18. Date Output Formats
General Format | Description | Example |
---|---|---|
ISO | ISO-8601 standard | 2001-06-25 12:24:00-07 |
SQL | Traditional SQL style | 06/25/2001 12:24:00.00 PDT |
Postgres | Original PostgreSQL style | Mon 25 Jun 12:24:00 2001 PDT |
German | Regional style for Germany | 25.06.2001 12:24:00.00 PDT |
As an example, you can use the following SQL statement to set the date style to SQL :
booktown=# SET DATESTYLE TO SQL; SET VARIABLE
If you perform a SELECT current_timestamp query after setting this variable, PostgreSQL should return the current time using the ISO format as instructed:
booktown=# SELECT current_timestamp; timestamp ---------------------------- 08/10/2001 13:25:55.00 PDT (1 row)
In addition to these general formats, PostgreSQL's date output format has two other variants which further describe how to display the date, shown in Table 3-19 : European and non-European (US). These determine whether the format is month followed by day, or vice versa. This variation can be applied on top of the previous four general formats with the same syntax to SET DATESTYLE , and will not modify your chosen format except for the arrangement of the month and day.
Table 3-19. Extended Date Output Formats
Month/Day Format | Description | Example |
---|---|---|
European | day/month/year | 12/07/2001 17:34:50.00 MET |
US , or Non-European | month/day/year | 07/12/2001 17:34:50.0 PST |
Furthermore, you may set both the general format and day/month convention by supplying both variables to the SET command, comma delimited. The order of these variables is not important to the SET command as long as the variables are not mutually exclusive (e.g., SQL and ISO ), as shown in Example 3-27 .
Example 3-27. Setting Date Formats
booktown=# SET DATESTYLE TO ISO,US; SET VARIABLE booktown=# SHOW DATESTYLE; NOTICE: DateStyle is ISO with US (NonEuropean) conventions SHOW VARIABLE booktown=# SET DATESTYLE TO NONEUROPEAN, GERMAN; SET VARIABLE booktown=# SHOW DATESTYLE; NOTICE: DateStyle is German with European conventions SHOW VARIABLE
If you do not specify a month/day format, a reasonable default will usually be chosen (e.g., European is the default for the German regional format).
While this is a convenient way to set the output format, it is important to notice that this is a run-time variable , which means that it exists only for the lifespan of your connected session to PostgreSQL when configured with the SET DATESTYLE command. There are two other ways to configure this format.
You may change the PGDATESTYLE environment variable used by the backend directly on postmaster 's start-up (e.g., adding a Bash export PGDATESTYLE="FORMAT" variable setting to the postgres user's .bash_profile file). This has the effect of being global to all date and time formatting performed by PostgreSQL.
You may change the PGDATESTYLE environment variable used by a client application (assuming it was written with the libpq library) on its session start-up, if you wish the client rather than the server to configure the output. For example, setting the PGDATESTYLE variable at a Bash prompt with the export command before starting psql sets the format such that psql will recognize it, and automatically configure itself for that style of output.
 
Continue to: