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
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
Standard ISO-8601 format: numeric year, month and day
ISO-8601: formatted numerically as complete year, month, day
ISO-8601: formatted numerically as 2-digit year, month, day
Non-European (US) format: numeric month, day and year
European format: numeric day, month and year
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
Similarly, Table 3-17 lists PostgreSQL-recognized abbreviations for weekday names.
Table 3-17. Day of the Week Abbreviations
Thu, Thur, Thurs
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.
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
Traditional SQL style
06/25/2001 12:24:00.00 PDT
Original PostgreSQL style
Mon 25 Jun 12:24:00 2001 PDT
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)
|Displaying Run-time DATESTYLE|
The SHOW command may be used to display this run-time variable at any time.
booktown=# SHOW DATESTYLE; NOTICE: DateStyle is SQL with US (NonEuropean) conventions SHOW VARIABLE
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
12/07/2001 17:34:50.00 MET
US , or Non-European
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.