{"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":28,"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=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/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":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202"},"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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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}]}}