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.
- Login to your cPanel.
- Under the Databases section, click on phpMyAdmin.
- From the left-hand menu click on your database name, in our example we’re using userna1_time.
- Then click on Browse beside the table you want to look at.
- 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.
- You can then click on the Edit link below the query to adjust the query in a new pop-up window.
- 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`
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.
- 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.
