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.

Note 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)
Note 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

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.