{"id":3379,"date":"2021-07-08T20:15:22","date_gmt":"2021-07-08T14:45:22","guid":{"rendered":"https:\/\/www.24x7serversupport.com\/blog\/?p=3379"},"modified":"2022-07-25T10:08:11","modified_gmt":"2022-07-25T04:38:11","slug":"working-with-mysql-mariadb-time-zones","status":"publish","type":"post","link":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/","title":{"rendered":"Working with MySQL-MariaDB Time Zones"},"content":{"rendered":"\n

For any program, whether it\u2019s a web app, API server, desktop software, tablet or phone app, working with the time zones is a pretty common task.<\/p>\n\n\n\n

In this article, I am going to show you how to install time zone databases to MySQL and MariaDB database server and use it. I have tested the steps shown in this article on CentOS 8 and Ubuntu 18.04 LTS. But it should work on CentOS\/RHEL 7+, Ubuntu 18.04+ and Debian 10+. So, let\u2019s get started.<\/p>\n\n\n\n

Prerequisites:<\/h3>\n\n\n\n

You must have MySQL or MariaDB installed on your Linux OS (i.e. CentOS\/RHEL, Ubuntu\/Debian). <\/p>\n\n\n\n

Installing Time Zone Data on CentOS\/RHEL:<\/h3>\n\n\n\n

In CentOS\/RHEL, the tzdata<\/strong> package provides time zone information. The tzdata <\/strong>package should be installed by default.<\/p>\n\n\n\n

If in any case, it is not installed, you can install it with the following commands: $ sudo dnf makecache
$ sudo dnf install tzdata<\/p>\n\n\n\n

NOTE:<\/u><\/strong> On CentOS\/RHEL 7, use yum<\/strong> instead of dnf<\/strong>.<\/p>\n\n\n\n

Installing Time Zone Data on Ubuntu\/Debian:<\/h3>\n\n\n\n

In Ubuntu\/Debian, the tzdata<\/strong> package provides time zone information. The tzdata <\/strong>package should be installed by default.<\/p>\n\n\n\n

If in any case, it is not installed, you can install it with the following commands: $ sudo apt update
$ sudo apt install tzdata<\/p>\n\n\n\n

Converting Time Zone Data to SQL:<\/h3>\n\n\n\n

The time zone data should be in the \/usr\/share\/zoneinfo\/<\/strong> directory of CentOS\/RHEL, and Ubuntu\/Debian OS. $ ls \/usr\/share\/zoneinfo\/<\/p>\n\n\n\n

As you can see, the time zone data is nicely arranged in different folders.<\/p>\n\n\n\n

The time zone data files are binary. You can\u2019t use them directly with MySQL\/MariaDB databases. $ cat \/usr\/share\/zoneinfo\/America\/Toronto<\/p>\n\n\n\n

You must convert the binary time zone data (from the \/usr\/share\/zoneinfo\/<\/strong> directory) to SQL using the mysql_tzinfo_to_sql<\/strong> program.<\/p>\n\n\n\n

To convert the time zone data to SQL, run mysql_tzinfo_to_sql<\/strong>as follows: $ mysql_tzinfo_to_sql \/usr\/share\/zoneinfo\/ > ~\/zoneinfo.sql<\/p>\n\n\n\n

A new file zoneinfo.sql<\/strong> should be created in your HOME directory. You can import the time zone information to your MySQL\/MariaDB database from this file.https:\/\/e63f88f522acc272bb65fe68ff1a3954.safeframe.googlesyndication.com\/safeframe\/1-0-38\/html\/container.html<\/p>\n\n\n\n

Importing Time Zone Data to MySQL\/MariaDB:<\/h3>\n\n\n\n

You can import the time zone information from the zoneinfo.sql<\/strong> file into the mysql<\/strong> database as follows: $ cat ~\/zoneinfo.sql | sudo mysql -u root mysql -p<\/p>\n\n\n\n

Now, type in your MySQL\/MariaDB database root password and press <Enter><\/strong>. The time zone information should be imported.<\/p>\n\n\n\n

Setting Default\/Global Time Zone in MySQL\/MariaDB:<\/h3>\n\n\n\n

By default, the default\/global time zone of MySQL\/MariaDB is set to the OS time zone. You can set a different default\/global time zone if you want. > SELECT @@time_zone;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

First, you have to find the time zone name which you want to set. You can find all the available time zone names with the following SQL statement: > SELECT name FROM mysql.time_zone_name;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

You can also search for your desired time zone name as follows: > SELECT name FROM mysql.time_zone_name WHERE name LIKE ‘%<search_term>%’;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Now, you can set your desired time zone as default\/global time zone as follows:<\/p>\n\n\n\n

> SETGLOBAL time_zone='<time_zone>’;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Your desired time zone should be set as default\/global time zone. > SELECT @@time_zone;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Working with Time Zones in MySQL\/MariaDB:<\/h1>\n\n\n\n

The CONVERT_TZ()<\/strong> function is used to convert the time zone of a datetime in MySQL\/MariaDB.<\/p>\n\n\n\n

The syntax of the CONVERT_TZ()<\/strong> function is:CONVERT_TZ(datetime, from_tz, to_tz)<\/p>\n\n\n\n

Here, from_tz<\/strong> and to_tz<\/strong> can be a time zone name (i.e. Asia\/Dhaka, America\/New_York), or time zone offset (i.e. +06:00, -02:00).<\/p>\n\n\n\n

The datetime<\/strong> is converted from from_tz<\/strong> to to_tz<\/strong> time zone.<\/p>\n\n\n\n

You can print the current timestamp (current date and time) of your computer with the following SQL statement: > SELECTCURRENT_TIMESTAMP();<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Now, let\u2019s say, you want to convert the time zone of the current date and time of your computer to  Europe\/London<\/strong>. To do that, you can run the CONVERT_TZ()<\/strong> function as follows: > SELECTCURRENT_TIMESTAMP() AS DT_Dhaka, CONVERT_TZ(CURRENT_TIMESTAMP(),
 @@time_zone, ‘Europe\/London’) AS DT_London;<\/p>\n\n\n\n

As you can see, the time zone of the current datetime is converted to Europe\/London<\/strong> successfully.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

You can also convert time zone of specific datetime as follows: > SET @dt=’2001-01-03 11:02:11′;
> SELECT @dt, CONVERT_TZ(@dt, ‘America\/Panama’, ‘Europe\/London’);<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

You can also convert the time zone of the datetime fields of a table. For demonstration, I will use a simple birthday table in this article.<\/p>\n\n\n\n

First, create a birthday<\/strong> table as follows: > CREATETABLE birthday( name VARCHAR(20) NOTNULL,
birth_timestamp DATETIMENOTNULL );<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

The birthday<\/strong> table has only 2 fields, name<\/strong> and birth_timestamp<\/strong> as you can see in the screenshot below. > DESCRIBE birthday;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Now, insert some dummy birthday data into the birthday<\/strong> table as follows: > INSERTINTO birthday VALUES(‘Bob’, ‘1997-10-11 12:11:11’),
(‘Alex’, ‘1987-01-11 01:41:01’),(‘Lily’, ‘2001-01-02 20:11:36’);<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Here are the dummy birthday data. > SELECT * FROM birthday;<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

Now, you can convert the time zone of all the birthday timestamps to Europe\/London<\/strong> as follows: > SELECT name, birth_timestamp, CONVERT_TZ(birth_timestamp, @@time_zone,
 ‘Europe\/London’) AS london_birth_timestamp FROM birthday;<\/p>\n\n\n\n

As you can see, the birthday time zones are converted correctly.<\/p>\n\n\n\n

\"\"<\/a><\/figure><\/div>\n\n\n\n

So, that\u2019s basically how you work with MySQL\/MariaDB time zones. Thanks for reading this article.<\/p>\n","protected":false},"excerpt":{"rendered":"

For any program, whether it\u2019s a web app, API server, desktop software, tablet or phone app, working with the time zones is a pretty common task. In this article, I am going to show you how to install time zone databases to MySQL and MariaDB database server and use it. I have tested the steps […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[30,237,38,33,238,336],"tags":[],"class_list":["post-3379","post","type-post","status-publish","format-standard","hentry","category-centos","category-centoswebpanel","category-cloudlinux","category-cpanel","category-cwp","category-debian"],"jetpack_publicize_connections":[],"yoast_head":"\nWorking with MySQL-MariaDB Time Zones | 24x7serversupport Blog<\/title>\n<meta name=\"description\" content=\"Working with MySQL-MariaDB Time Zones\" \/>\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.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working with MySQL-MariaDB Time Zones | 24x7serversupport Blog\" \/>\n<meta property=\"og:description\" content=\"Working with MySQL-MariaDB Time Zones\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/\" \/>\n<meta property=\"og:site_name\" content=\"24x7serversupport Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-08T14:45:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-25T04:38:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png\" \/>\n<meta name=\"author\" content=\"24x7support\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@24x7serversuppo\" \/>\n<meta name=\"twitter:site\" content=\"@24x7serversuppo\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"24x7support\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/\",\"url\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/\",\"name\":\"Working with MySQL-MariaDB Time Zones | 24x7serversupport Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png\",\"datePublished\":\"2021-07-08T14:45:22+00:00\",\"dateModified\":\"2022-07-25T04:38:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/#\/schema\/person\/decfb5fad6bde6ac6822d4e965c6d401\"},\"description\":\"Working with MySQL-MariaDB Time Zones\",\"breadcrumb\":{\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage\",\"url\":\"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png\",\"contentUrl\":\"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.24x7serversupport.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working with MySQL-MariaDB Time Zones\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/#website\",\"url\":\"https:\/\/www.24x7serversupport.com\/blog\/\",\"name\":\"24x7serversupport Blog\",\"description\":\"Linux | CPanel | WHM | webhosting| Plesk | DirectAdmin | CentOs | Debian | Ubuntu Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.24x7serversupport.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.24x7serversupport.com\/blog\/#\/schema\/person\/decfb5fad6bde6ac6822d4e965c6d401\",\"name\":\"24x7support\",\"url\":\"https:\/\/www.24x7serversupport.com\/blog\/author\/24x7support\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Working with MySQL-MariaDB Time Zones | 24x7serversupport Blog","description":"Working with MySQL-MariaDB Time Zones","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.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/","og_locale":"en_US","og_type":"article","og_title":"Working with MySQL-MariaDB Time Zones | 24x7serversupport Blog","og_description":"Working with MySQL-MariaDB Time Zones","og_url":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/","og_site_name":"24x7serversupport Blog","article_published_time":"2021-07-08T14:45:22+00:00","article_modified_time":"2022-07-25T04:38:11+00:00","og_image":[{"url":"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png"}],"author":"24x7support","twitter_card":"summary_large_image","twitter_creator":"@24x7serversuppo","twitter_site":"@24x7serversuppo","twitter_misc":{"Written by":"24x7support","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/","url":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/","name":"Working with MySQL-MariaDB Time Zones | 24x7serversupport Blog","isPartOf":{"@id":"https:\/\/www.24x7serversupport.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage"},"image":{"@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage"},"thumbnailUrl":"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png","datePublished":"2021-07-08T14:45:22+00:00","dateModified":"2022-07-25T04:38:11+00:00","author":{"@id":"https:\/\/www.24x7serversupport.com\/blog\/#\/schema\/person\/decfb5fad6bde6ac6822d4e965c6d401"},"description":"Working with MySQL-MariaDB Time Zones","breadcrumb":{"@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#primaryimage","url":"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png","contentUrl":"https:\/\/linuxhint.com\/wp-content\/uploads\/2020\/03\/8-33.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.24x7serversupport.com\/blog\/working-with-mysql-mariadb-time-zones\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.24x7serversupport.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Working with MySQL-MariaDB Time Zones"}]},{"@type":"WebSite","@id":"https:\/\/www.24x7serversupport.com\/blog\/#website","url":"https:\/\/www.24x7serversupport.com\/blog\/","name":"24x7serversupport Blog","description":"Linux | CPanel | WHM | webhosting| Plesk | DirectAdmin | CentOs | Debian | Ubuntu Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.24x7serversupport.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.24x7serversupport.com\/blog\/#\/schema\/person\/decfb5fad6bde6ac6822d4e965c6d401","name":"24x7support","url":"https:\/\/www.24x7serversupport.com\/blog\/author\/24x7support\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/posts\/3379","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/comments?post=3379"}],"version-history":[{"count":2,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/posts\/3379\/revisions"}],"predecessor-version":[{"id":3437,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/posts\/3379\/revisions\/3437"}],"wp:attachment":[{"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/media?parent=3379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/categories?post=3379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.24x7serversupport.com\/blog\/wp-json\/wp\/v2\/tags?post=3379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}