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

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

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

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

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.