Permanently Set the PostgreSQL datestyle to ISO, DMY, European or Australian

I keep setting up servers with default postgresql.conf which is MDY

me@jaykilleen.com wrote this about 5 years ago and it was last updated about 5 years ago.


← Back to the Posts

I have done this a few times now and even found my own question answered on Stackoverflowpostgresql-set-datestyle-to-european)

Putting it here now so I remember for future dum dums.

When setting datestyle in psql it is only changed for the current session. So usually lost if you restart or ssh back out of the server. This can be frustrating because on your local machine your session is usually persisted longer or you can already permanently set the datestyle. Maybe this is due to installation when you are in your home country vs the server setup.

Anyway, I ssh'd into the machine and did one of two options.

Change the postgresql.conf

sudo nano /etc/postgresql/9.5/main/postgresql.conf

Find the line for the datestyle and change to ISO, dmy

SET Datestyle for the Database

Login to psql and set the datestyle for the entire database using

ALTER DATABASE database_name SET datestyle TO 'ISO, DMY';