setting timezone for MySQL using PHPMyAdmin

Question

Currently whenever a user creates a new account, I have a Creation_date column that has datatype timestamp. Currently this timestamp reads 3 hours ahead of EST (if it's 5PM in Boston it reads 8PM). Is there a way to change the timezone to EST? What timezone is it set to currently?

Answer 1

Accordingly with this question

SET SESSION time_zone = '+8:00'

Answer 2

TIMESTAMP values are converted to UTC when inserted into the DB and converted back to the current timezone set in MySQL on retrieval. The other answers show how to set the timezone in MySQL which will determine what value comes out of the database.

Another option is to use a DATETIME column, store ALL of your dates in UTC, and convert them to the desired timezone in your application (PHP or wherever you get the values from).

Answer 3

This has to do with MySQL's timezone.

You can set it per connection (e.g. via PHPMyAdmin) with the following:

SET time_zone = timezone;

However, this will reset if MySQL restarts. So it is better set at the server level. Which, I assume, you can't.

I encourage you to read more from the MySQL Docs.