{"id":9901,"date":"2017-03-29T13:59:50","date_gmt":"2017-03-29T11:59:50","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/"},"modified":"2017-03-29T13:59:50","modified_gmt":"2017-03-29T11:59:50","slug":"can-i-do-it-with-postgresql-10-timezones","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/","title":{"rendered":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones"},"content":{"rendered":"

This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone<\/a> which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time<\/a>:<\/p>\n

\nSQL> select sessiontimezone from dual;\n\nSESSIONTIMEZONE\n---------------------------------------------------------------------------\n+02:00\n<\/pre>\n
This is fine as I am based in Switzerland and we skipped one hour in the night from last Saturday to Sunday \ud83d\ude42 <\/p>\n
<\/p>\n
How can we do something similar in PostgreSQL? To check the current time zone of your session:<\/p>\n
\n(postgres@[local]:4445) [postgres] > show timezone;\n   TimeZone   \n--------------\n Europe\/Vaduz\n(1 row)\n<\/pre>\n
Or:<\/p>\n
\n(postgres@[local]:4445) [postgres] > select current_setting('timezone');\n current_setting \n-----------------\n Europe\/Vaduz\n(1 row)\n<\/pre>\n
So, PostgreSQL will not show you the offset to UTC but the name of the time zone as specified by the Internet Assigned Numbers Authority (IANA)<\/a>. When you want to have the offset to UTC you can do something like this:<\/p>\n
\n(postgres@[local]:4445) [postgres] > select age(now(),now() at time zone 'UTC');\n   age    \n----------\n 02:00:00\n(1 row)\n<\/pre>\n
You can do it using the extract function<\/a> as well:<\/p>\n
\n(postgres@[local]:4445) [postgres] > select extract( timezone from now() ) \/ 60 \/60;\n ?column? \n----------\n        2\n(1 row)\n<\/pre>\n
How can you change the session time zone? One way is to set the PGTZ environment variable<\/a> before starting a new session when you use a libpq client<\/a>: <\/p>\n
\npostgres@centos7:\/home\/postgres\/ [PG3] export PGTZ=Europe\/Berlin\npostgres@centos7:\/home\/postgres\/ [PG3] psql postgres\npsql.bin (9.6.2.7)\nType \"help\" for help.\n\n(postgres@[local]:4445) [postgres] > show timezone;\n   TimeZone    \n---------------\n Europe\/Berlin\n(1 row)\n<\/pre>\n
The other way is to directly set it in the session:<\/p>\n
\nTime: 1.048 ms\n(postgres@[local]:4445) [postgres] > set time zone 'Europe\/Paris';\nSET\nTime: 82.903 ms\n(postgres@[local]:4445) [postgres] > show timezone;\n   TimeZone   \n--------------\n Europe\/Paris\n(1 row)\n<\/pre>\n
Of course you can also set the timezone parameter in postgresql.conf.<\/p>\n
To get the current timestamp you can use:<\/p>\n
\n(postgres@[local]:4445) [postgres] > SELECT current_timestamp;\n        current_timestamp         \n----------------------------------\n 29-MAR-17 15:41:59.203485 +02:00\n(1 row)\n<\/pre>\n
And finally, to calculate the current time in another time zone you can do something like this:<\/p>\n
\n(postgres@[local]:4445) [postgres] > SELECT current_time AT TIME ZONE 'Europe\/Zurich', current_time AT TIME ZONE 'US\/Samoa';\n      timezone      |      timezone      \n--------------------+--------------------\n 15:43:05.634124+02 | 02:43:05.634124-11\n(1 row)\n<\/pre>\n
All the time zone names can be found in pg_timezone_names<\/a>:<\/p>\n
\n(postgres@[local]:4445) [postgres] > select * from pg_timezone_names;\n               name               | abbrev | utc_offset | is_dst \n----------------------------------+--------+------------+--------\n America\/North_Dakota\/Beulah      | CDT    | -05:00:00  | t\n America\/North_Dakota\/Center      | CDT    | -05:00:00  | t\n America\/North_Dakota\/New_Salem   | CDT    | -05:00:00  | t\n America\/Argentina\/Ushuaia        | ART    | -03:00:00  | f\n...\n<\/pre>\n
Hope this helps…<\/p>\n","protected":false},"excerpt":{"rendered":"
This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time: SQL> select sessiontimezone from dual; SESSIONTIMEZONE ————————————————————————— +02:00 This is fine […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-9901","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCan I do it with PostgreSQL? \u2013 10 \u2013 Timezones - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones\" \/>\n<meta property=\"og:description\" content=\"This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time: SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- +02:00 This is fine […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-29T11:59:50+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones\",\"datePublished\":\"2017-03-29T11:59:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/\"},\"wordCount\":233,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/\",\"name\":\"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-03-29T11:59:50+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-10-timezones\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/","og_locale":"en_US","og_type":"article","og_title":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones","og_description":"This post is inspired by a question we received from a customer: In Oracle there is the sessiontimezone which returns the time zone of the session. Asking for the time zone of the session in Oracle returns you the offset to the UTC time: SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- +02:00 This is fine […]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/","og_site_name":"dbi Blog","article_published_time":"2017-03-29T11:59:50+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones","datePublished":"2017-03-29T11:59:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/"},"wordCount":233,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/","url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/","name":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-03-29T11:59:50+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-10-timezones\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can I do it with PostgreSQL? \u2013 10 \u2013 Timezones"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9901","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9901"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9901\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9901"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9901"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9901"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9901"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}