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.

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.

The steps below will walk you through an example database that I've set up that simply has 2 database entries with some time stamps. We'll 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.

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

    SELECT CONVERT_TZ(Time, '-05:00', '-06:00'), Data FROM `time`

    select-cst-time-zone

    You should have noticed that both of our time stamps adjusted from 11:00:00 down to 10:00:00, showing that our time zone conversion command was successful.

  8. You can also use the MySQL commands DATE_ADD and DATE_SUB to add or subtract hours as well:

    SELECT DATE_ADD(Time, INTERVAL 1 HOUR), Data FROM `time`

    SELECT DATE_SUB(Time, INTERVAL 1 HOUR), Data FROM `time`

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.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Like this Article?

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

0 Questions & Comments

Post a comment

Back to first comment | top

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!