{"id":483,"date":"2012-11-26T16:20:58","date_gmt":"2012-11-26T21:20:58","guid":{"rendered":"https:\/\/www.inmotionhosting.com\/support\/2012\/11\/26\/how-to-convert-mysql-time\/"},"modified":"2021-08-16T23:27:50","modified_gmt":"2021-08-17T03:27:50","slug":"how-to-convert-mysql-time","status":"publish","type":"post","link":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/","title":{"rendered":"How to convert MySQL time"},"content":{"rendered":"

In this article we\u2019ll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server\u2019s time stamp to your own local time zone so you can more easily go through the data.<\/p>\n

On shared servers the MySQL default timezone will be set to the coast that server resides on, so if your server starts with an (ecbiz) this would be on the East Coast and use EST as the time zone. Where if your shared server simply begins with (biz) then this would be on the West Coast using the PST time zone. If you are using a VPS (Virtual Private Server) or a dedicated server hosting plan you can also read about how to change the MySQL server time zone<\/a>.<\/p>\n

The steps below will walk you through an example database that I\u2019ve set up that simply has 2 database entries with some time stamps. We\u2019ll go through a few options that you have to adjust the time stamps on the fly to retrieve the correct time stamp that you want.<\/p>\n

    \n
  1. Login to your cPanel<\/a>.<\/li>\n
  2. Under the Databases<\/strong> section, click on phpMyAdmin<\/strong>.
    \n    \"cpanel-click-on-phpmyadmin\"<\/a>\n
    <\/div>\n<\/li>\n
  3. From the left-hand menu click on your database name, in our example we\u2019re using userna1_time<\/strong>.
    \n    \"click-on-time-table\"<\/a>\n
    <\/div>\n<\/li>\n
  4. Then click on Browse<\/strong> beside the table you want to look at.
    \n    \"click-browse-on-time-table\"<\/a>\n
    <\/div>\n<\/li>\n
  5. You should see with the default query we are simply doing a SELECT * FROM \u2018time\u2019<\/strong>, which should return all entries that have a value in the Time<\/strong> column.
    \n    \"select-default-from-time\"<\/a>\n
    <\/div>\n<\/li>\n
  6. You can then click on the Edit<\/strong> link below the query to adjust the query in a new pop-up window.
    \n    \"click-on-edit-query\"<\/a>\n
    <\/div>\n<\/li>\n
  7. You can adjust the query to change the time stamps of our data from EST (GMT -05:00<\/strong>) to CST (GMT -06:00<\/strong>) using the MySQL CONVERT_TZ<\/strong> command:.
    \nSELECT CONVERT_TZ(Time, '-05:00', '-06:00'), Data FROM `time`<\/code>     \"select-cst-time-zone\"<\/a>\n
    <\/div>\n

    You should have noticed that both of our time stamps adjusted from 11:00:00<\/strong> down to 10:00:00<\/strong>, showing that our time zone conversion command was successful.<\/p><\/li>\n

  8. You can also use the MySQL commands DATE_ADD<\/strong> and DATE_SUB<\/strong> to add or subtract hours as well:\n

    SELECT DATE_ADD(Time, INTERVAL 1 HOUR), Data FROM `time`<\/code><\/p>\n

    SELECT DATE_SUB(Time, INTERVAL 1 HOUR), Data FROM `time`<\/code><\/p><\/li>\n<\/ol>\n

    You should now understand how you can change or convert the MySQL stored time stamps from one time zone to another so that you can more easily work with your data.<\/p>\n","protected":false},"excerpt":{"rendered":"

    In this article we\u2019ll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server\u2019s time stamp to your own local time zone so you can more easily go through the data. Read More ><\/a><\/p>\n","protected":false},"author":57014,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4305],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-databases"],"acf":[],"yoast_head":"\nHow to convert MySQL time | InMotion Hosting<\/title>\n<meta name=\"description\" content=\"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.\" \/>\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.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to convert MySQL time | InMotion Hosting\" \/>\n<meta property=\"og:description\" content=\"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\" \/>\n<meta property=\"og:site_name\" content=\"InMotion Hosting Support Center\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/inmotionhosting\/\" \/>\n<meta property=\"article:published_time\" content=\"2012-11-26T21:20:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-08-17T03:27:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png\" \/>\n<meta name=\"author\" content=\"InMotion Hosting Contributor\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/InMotionHosting\" \/>\n<meta name=\"twitter:site\" content=\"@InMotionHosting\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"InMotion Hosting Contributor\" \/>\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.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\"},\"author\":{\"name\":\"InMotion Hosting Contributor\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/person\/f9a4fc454cd1df128ee8e898d30d4644\"},\"headline\":\"How to convert MySQL time\",\"datePublished\":\"2012-11-26T21:20:58+00:00\",\"dateModified\":\"2021-08-17T03:27:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\"},\"wordCount\":390,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png\",\"articleSection\":[\"Working with Databases\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\",\"url\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\",\"name\":\"How to convert MySQL time | InMotion Hosting\",\"isPartOf\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png\",\"datePublished\":\"2012-11-26T21:20:58+00:00\",\"dateModified\":\"2021-08-17T03:27:50+00:00\",\"description\":\"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage\",\"url\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png\",\"contentUrl\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png\",\"width\":501,\"height\":106,\"caption\":\"cpanel-click-on-phpmyadmin\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.inmotionhosting.com\/support\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to convert MySQL time\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#website\",\"url\":\"https:\/\/www.inmotionhosting.com\/support\/\",\"name\":\"InMotion Hosting Support Center\",\"description\":\"Web Hosting Support & Tutorials\",\"publisher\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.inmotionhosting.com\/support\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#organization\",\"name\":\"InMotion Hosting\",\"url\":\"https:\/\/www.inmotionhosting.com\/support\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2023\/02\/inmotion-hosting-logo-yoast.jpg\",\"contentUrl\":\"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2023\/02\/inmotion-hosting-logo-yoast.jpg\",\"width\":696,\"height\":696,\"caption\":\"InMotion Hosting\"},\"image\":{\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/inmotionhosting\/\",\"https:\/\/x.com\/InMotionHosting\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/person\/f9a4fc454cd1df128ee8e898d30d4644\",\"name\":\"InMotion Hosting Contributor\",\"description\":\"InMotion Hosting contributors are highly knowledgeable individuals who create relevant content on new trends and troubleshooting techniques to help you achieve your online goals!\",\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/inmotion-hosting\/\",\"https:\/\/x.com\/https:\/\/twitter.com\/InMotionHosting\"],\"url\":\"https:\/\/www.inmotionhosting.com\/support\/author\/inmotion-hosting-contributor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to convert MySQL time | InMotion Hosting","description":"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.","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.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/","og_locale":"en_US","og_type":"article","og_title":"How to convert MySQL time | InMotion Hosting","og_description":"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.","og_url":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/","og_site_name":"InMotion Hosting Support Center","article_publisher":"https:\/\/www.facebook.com\/inmotionhosting\/","article_published_time":"2012-11-26T21:20:58+00:00","article_modified_time":"2021-08-17T03:27:50+00:00","og_image":[{"url":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png","type":"","width":"","height":""}],"author":"InMotion Hosting Contributor","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/InMotionHosting","twitter_site":"@InMotionHosting","twitter_misc":{"Written by":"InMotion Hosting Contributor","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#article","isPartOf":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/"},"author":{"name":"InMotion Hosting Contributor","@id":"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/person\/f9a4fc454cd1df128ee8e898d30d4644"},"headline":"How to convert MySQL time","datePublished":"2012-11-26T21:20:58+00:00","dateModified":"2021-08-17T03:27:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/"},"wordCount":390,"commentCount":0,"publisher":{"@id":"https:\/\/www.inmotionhosting.com\/support\/#organization"},"image":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage"},"thumbnailUrl":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png","articleSection":["Working with Databases"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/","url":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/","name":"How to convert MySQL time | InMotion Hosting","isPartOf":{"@id":"https:\/\/www.inmotionhosting.com\/support\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage"},"image":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage"},"thumbnailUrl":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png","datePublished":"2012-11-26T21:20:58+00:00","dateModified":"2021-08-17T03:27:50+00:00","description":"In this article we'll discuss the ability you have within MySQL to convert time storage such as time stamps and time zones to better meet your local needs. In many cases you might need to convert the server's time stamp to your own local time zone so you can more easily go through the data.","breadcrumb":{"@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#primaryimage","url":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png","contentUrl":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2012\/11\/website_cpanel_cpanel-split_cpanel-click-on-phpmyadmin.png","width":501,"height":106,"caption":"cpanel-click-on-phpmyadmin"},{"@type":"BreadcrumbList","@id":"https:\/\/www.inmotionhosting.com\/support\/server\/databases\/how-to-convert-mysql-time\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.inmotionhosting.com\/support\/"},{"@type":"ListItem","position":2,"name":"How to convert MySQL time"}]},{"@type":"WebSite","@id":"https:\/\/www.inmotionhosting.com\/support\/#website","url":"https:\/\/www.inmotionhosting.com\/support\/","name":"InMotion Hosting Support Center","description":"Web Hosting Support & Tutorials","publisher":{"@id":"https:\/\/www.inmotionhosting.com\/support\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.inmotionhosting.com\/support\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.inmotionhosting.com\/support\/#organization","name":"InMotion Hosting","url":"https:\/\/www.inmotionhosting.com\/support\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/logo\/image\/","url":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2023\/02\/inmotion-hosting-logo-yoast.jpg","contentUrl":"https:\/\/www.inmotionhosting.com\/support\/wp-content\/uploads\/2023\/02\/inmotion-hosting-logo-yoast.jpg","width":696,"height":696,"caption":"InMotion Hosting"},"image":{"@id":"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/inmotionhosting\/","https:\/\/x.com\/InMotionHosting"]},{"@type":"Person","@id":"https:\/\/www.inmotionhosting.com\/support\/#\/schema\/person\/f9a4fc454cd1df128ee8e898d30d4644","name":"InMotion Hosting Contributor","description":"InMotion Hosting contributors are highly knowledgeable individuals who create relevant content on new trends and troubleshooting techniques to help you achieve your online goals!","sameAs":["https:\/\/www.linkedin.com\/company\/inmotion-hosting\/","https:\/\/x.com\/https:\/\/twitter.com\/InMotionHosting"],"url":"https:\/\/www.inmotionhosting.com\/support\/author\/inmotion-hosting-contributor\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"primary_category":null,"_links":{"self":[{"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/users\/57014"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/comments?post=483"}],"version-history":[{"count":3,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/posts\/483\/revisions"}],"predecessor-version":[{"id":85287,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/posts\/483\/revisions\/85287"}],"wp:attachment":[{"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/media?parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/categories?post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inmotionhosting.com\/support\/wp-json\/wp\/v2\/tags?post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}