Changing Timezone in Postgresql, Ubuntu

Timezone and locale are integral concepts that one should be aware of when implementing a System. For example. if you create a database in PostgreSql, it will use the default System settings for the database. Lets say you have a server in UK and your client is US. If the client tries to retrieve the current date or time, it is going to show the UK time and not the US one.

Today I encountered a different issue in our client place where the Ubuntu timezone and PostgreSql timezone were showing a incorrect values. Instead of Indian Standard Time (IST) it was showing Pacific Daylight Time(PDT). These are the steps that I followed to change the timezone to IST in PostgreSql and Ubuntu.

Changing timezone in Ubuntu:

sudo dpkg-reconfigure tzdata

You will be prompted to choose the continent and then the area. Provided screen shots where we configure the location.

Use TAB key to navigate to the OK button and then press Enter.

The changes will get reflected immediately. To check it you can open a new terminal and type ‘date’. The output is provided below.

Changing timezone in PostgreSql Database:

1. Select the name of the timzone you want to set.

SELECT * FROM pg_timezone_nam<wbr>es;</wbr>

2. Update the corresponding timezone, in my case ‘Asia/Kolkata’.

ALTER DATABASE postgres SET timezone TO 'Asia/Kolkata';

3. Use Select now() to check the current timezone setting.

For more such articles on Ubuntu, Postgres and Openbravo Subscribe here or here.

About Shankar Balachandran
Technical Manager, Openbravo operations, Kailao Consulting Private Limited

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: