postgres default timezone

Question

I installed PostgreSQL 9 and the time it is showing is 1 hour behind the server time   Running Select NOW   shows  2011-07-12 11 51 50 453842 00  The server date shows  Tue Jul 12 12 51 40 BST 2011  It is 1 hour behind but the timezone shown in phppgadmin is  TimeZone    Etc GMT0  I have tried going into the postgresql conf and setting      timezone   GMT   then running a restart but no change   Any ideas I thought it would have just used the server timezone but obviously not    SOLUTION   I did set to GMT before and it was an hour behind  after searching around turns out that I needed to set it to Europe London  This takes into account the  1 hour in British summer time  GMT does not

User · Accepted Answer

The time zone is a session parameter  So  you can change the timezone for the current session    See the doc   set timezone TO  GMT       Or  more closely following the SQL standard  use the SET TIME ZONE command  Notice two words for  TIME ZONE  where the code above uses a single word  timezone    SET TIME ZONE  UTC     The doc explains the difference      SET TIME ZONE extends syntax defined in the SQL standard  The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications  All other SET features are PostgreSQL extensions

User · Answer

Maybe not related to the question  but I needed to use CST  set the system timezone to the desired tz  America      and then in postgresql conf set the value of the timezone to  localtime  and it worked as a charm  current time printing the right time  Postgresql 9 5 on Ubuntu 16

User · Answer

To acomplish the timezone change in Postgres 9 1 you must   1 - Search in your  timezones  folder in  usr share postgresql 9 1  for the appropiate file  in my case would be  America txt   in it  search for the closest location to your zone and copy the first letters in the left column   For example  if you are in  New York  or  Panama  it would be  EST       - EST  Eastern Standard Time  Australia  EST    -18000      Eastern Standard Time  America                          America New York                          America Panama    2 - Uncomment the  timezone  line in your postgresql conf file and put your timezone as shown    intervalstyle    postgres   timezone     defaults to server environment setting   timezone    EST   timezone abbreviations    EST        Select the set of available time zone                                           abbreviations   Currently  there are                                             Default                                             Australia   3 - Restart Postgres

User · Answer

The accepted answer by Muhammad Usama is correct  Configuration Parameter Name That answer shows how to set a Postgres-specific configuration parameter with the following  SET timezone TO  UTC       where timezone is not a SQL command  it is the name of the configuration parameter  See the doc for this  Standard SQL Command Alternatively  you can use the SQL command defined by the SQL spec  SET TIME ZONE  In this syntax a pair of words TIME ZONE replace  quot timezone quot   actual SQL command versus parameter name   and there is no  quot TO quot   SET TIME ZONE  UTC    Both this command and the one above have the same effect  to set the value for the current session only  To make the change permanent  see this sibling answer  See the doc for this  Time Zone Name You can specify a proper time zone name  Most of these are continent region  SET TIME ZONE  Africa Casablanca       or    SET TIME ZONE  America Montreal    Avoid the 3 or 4 letter abbreviations such as EST or IST as they are neither standardized nor unique  See Wikipedia for list of time zone names  Get current zone To see the current time zone for a session  try either of the following statements  Technically we are calling the SHOW command to display a run-time parameter  SHOW timezone       or    SHOW time zone     US Pacific

User · Answer

What if you set the timezone of the role you are using  ALTER ROLE my db user IN DATABASE my database     SET  quot TimeZone quot  TO  UTC    Will this be of any use

User · Answer

Choose a timezone from   SELECT   FROM pg timezone names    And set as below given example   ALTER DATABASE postgres SET timezone TO  Europe Berlin     Use your DB name in place of postgres in above statement

User · Answer

Note many third-party clients have own timezone settings overlapping any Postgres server and or session settings   E g  if you re using  IntelliJ IDEA 2017 3   or DataGrips   you should define timezone as    DB source properties  -   Advanced  tab -   VM Options   -Duser timezone UTC 06 00  otherwise you will see  UTC  despite of whatever you have set anywhere else

User · Answer

In addition to the previous answers  if you use the tool pgAdmin III you can set the time zone as follows    Open Postgres config via Tools   Server Configuration   postgresql conf Look for the entry timezone and double click to open Change the Value Reload Server to apply configuration changes  Play button on top or via services

[postgresql] postgres default timezone

Examples related to postgresql