How to change datestyle in PostgreSQL?

Question

In postgres I have a table with date column. Now postgres allows me to write date in Y-m-d format. But I need date in d/m/Y format. How to change it?

When I do:

   show datestyle; 

I get:

 "ISO, DMY" 

And input date in table in this format 13/02/2009

But when I close and open table again I see this 2009-02-13. JDBC gives me date in this format too. What am I doing wrong?

Answer 1

you also can use the command

set datestyle to [your new datestyle]; 

in the console of postgreSQL.

Answer 2

yyyy-mm-dd is the recommended format for date field, its the ISO 8601 format.

You can change the format in the postgresql.conf file.

The document states

The date/time styles can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client. The formatting function to_char is also available as a more flexible way to format date/time output.

Hope this helps!