CODEWITHSUNDEEP
phpmysqlsqltimezoneunix-timestamp
Brett
Brett

Reputation: 20049

PHP & MySQL Timezones whilst supporting user-defined timezones

I'm working on something where the user can select their own timezone and the software will be able to be used by others on their sites as well but I want to make sure that the timezone within the database is always set to UTC.

Now I know how you set the default timezone for PHP, such as:

date_default_timezone_set('Australia/Sydney');

...but I'm not sure how to make sure MySQL is using UTC? ...and even once you have made sure it is using UTC I guess you would have to convert your PHP dates/times into UTC before passing it to the database?

I guess I am wondering about many different date formats such as TIMESTAMP, DATETIME & even UNIX EPOCH integer timestamps which would simply be stored as a int datatype for example.

Then there is the whole retrieving dates/times from the DB and converting it to the respective timezone and lastly how does DST come into all of this?

I know there is a lot of similar questions out there, but I guess none really answered all my questions.

Upvotes: 0

Views: 103

Answers (2)

Mjh
Mjh

Reputation: 2945

MySQL's data type timestamp stores the dates in UTC. For this to work properly, MySQL uses server's time zone and does the date conversion. It converts the date from servers's current time zone to UTC for storage. This implies that the database server should never change its time zone for this feature to work properly.

When you send the data to such a database, you send the UTC time as well. The easiest way to do this is to format a result of time() according to what MySQL wants (m-d-Y H:i:s).

In PHP, when you format the date for insertion to MySQL, it's the best to use DateTime class. It lets you offset the date with the time zone information, meaning that you don't have to use date_default_timezone_set function - that can lead to mistakes.

An example of DateTime in action: 

$date = '1.12.2015 13:37:37'; // Format is day.month.year hour:minute:second

// We create DateTime from custom date format, for the person who resides in Australia/Sydney time zone
$dt = DateTime::createFromFormat('d.m.Y H:i:s', $date, new DateTimeZone('Australia/Sydney'); 

// Now we change the date's time zone into UTC, and we can insert it into MySQL
$dt->setTimeZone(new DateTimeZone('UTC'));

// This is the formatted date-string that can be safely inserted into MySQL
$date_string_for_mysql = $dt->format('m-d-Y H:i:s');

Alternatively, you can use int type in MySQL for timestamp storage and insert result of time() but this has a huge disadvantage of not being able to use date-related functions.

Upvotes: 3

Ram Sharma
Ram Sharma

Reputation: 8819

for current session of mysql you can try something like 

SET time_zone = timezonename;

for more details you can also look into this answer https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp

Upvotes: 1

Related Questions