Monday, November 7, 2011

Postgresql - set datestyle

Sometimes you're moving data and format differences arise. One of the most common differences is between date formats.
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 formatDescriptionExample
ISOISO-8601 standard2001-06-25 12:24:00-07
SQLTraditional SQL style06/25/2001 12:24:00.00 PDT
PostgresOriginal PostgreSQL styleMon 25 Jun 12:24:00 2001 PDT
GermanRegional style for Germany25.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; 

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; 
08/10/2001 13:25:55.00 PDT
(1 row)

The SHOW command can be used to display the current value of the DATESTYLE variable while PostgreSQL is running.
booktown=#  SHOW DATESTYLE; 
NOTICE: DateStyle is SQL with US (NonEuropean) conventions

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 (U.S.). These determine whether the format is day followed by month, 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.

No comments:

Post a Comment