Php time zone mysql

Working with timezone in PHP & MySQL

How should I go about dealing with timezone. Is it safe to just store the offset for the user? Or should I also have the Area/Location? When I compared the offset values in Wikipedia and PHP, some doesn’t match. Which should I trust? Lastly how should I go about with it in PHP. Can I just do a «Time — Server Offset + User Offset»?

4 Answers 4

Area and location is important if you care about retaining all the data. The best way (in my opinion) to store dates is to store a UTC timestamp + the location.

Just the offset may be enough for certain calculations, but it could not be enough if you have a timestamp, and want to know the exact time for something like «+1 day». As this varies in different countries with different rules for daylight savings time.

So if you want to be absolutely certain you are not ‘losing information’ and unable to do time-based calculations in the future, store the UTC timestamp and the olson id (e.g.: Europe/Amsterdam).

To answer your second question, if you have these two pieces of information, you can easily reconstruct it with DateTime:

$dt = new DateTime('@' . $timeStamp); // Now convert it to the users timezone $dt->setTimeZone(new DateTimeZone('Europe/Berlin')); // Now you have a 'DateTime' object which you can easily display with the ->format function. 

I personally prefer to store timestamps as integers. The TIMESTAMP type does automatic conversion, and I feel it’s better to let the PHP application handle this, this makes especially sense for what I think your use-case is (simple localization for users).

Using DATETIME works too, but the storage requirements are much higher than just using an integer. If you do prefer DATETIME, try to make it a rule within your application to store every value always as UTC, as there is never any confusion especially in relation to DST transitions and law changes in your local timezone.

If you simply want to show times on your web application calculated based on the users’ local timezone, the offset is useless. The offset changes twice a year for most countries, and almost every year one or two country changes when this happens.

You only need the location if you use PHP’s awesome DateTime and DateTimeZone objects.

People tend to confuse dates and times in PHP applications and sending around these values in many different formats (strings, ints, etc) and mix GMT and UTC. Try to make it a rule for yourself to only ever send around DateTime objects in function arguments and return values, so you can typehint and there is never any doubt in what format a variable is in. It will be worth it.

Источник

PHP/MySQL timestamp and timezones

If I grab the current timestamp using the NOW() function in MySQL can I grab that field via php and give that time in different time zones? Basically converting current time in the current timezone to another timezone?

2 Answers 2

$gmt = new DateTimeZone("GMT"); $datetimeInGMT = new DateTime($now, $gmt); 

It also takes locations in the form continent/city , e.g. Europe/London .

If your datetime is non-UTC, you can use setTimezone :

$datetimeInGMT = new DateTime($now, new DateTimeZone("America/New_York")); $datetimeInGMT->setTimezone(new DateTimeZone("GMT")); 

Right but what if I already have the timestamp in a different timezone? Can I convert it to a different timezone?

+1 (and if I could, another +1 for DateTime ) but don’t forget that for this to work, you need to know the mySQL server’s current time zone to get the start date right! MySQL’s time zone setting can differ from the system’s zone.

Also, if you didn’t want to use the DateTime class (no reason why not, just saying) and you had a valid timestamp or date (like 5:00 PM, Jan 1, 2011, or 2011-01-01 20:00:00), you could do something like date(«whatever format»,strtotime($timestamp) + $theoffset * 3600) , where $timestamp is the date and $theoffset is the difference between the $timestamp’s timezone and the timezone you’re looking to change it to in hours.

MySQL’s timezone handling is much more sophisticated than PHP’s. It handles simple timezone (EST, PST, etc) and what i will call ‘regional timezones’ (America/Eastern). Using regional zones uses proper conversion including daylight savings time, even when DST rules have changed over the years.

What I’ve done is store all my datetimes as UTC using MySQL function UTC_TIMESTAMP(). Then, in queries, I use MySQL function CONVERT_TZ() to my target timezone. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Note: you may need to update your timezone tables. http://dev.mysql.com/downloads/timezones.html

Since PHP 5, PHP is the more sophisticated of the two: The DateTime class fully supports time zones. A DateTime timestamp can be set and cross-converted to any zone. MySQL is unable to store timezone info in a DATETIME field at all. Still, CONVERT_TZ is a valid approach of course

This question is in a collective: a subcommunity defined by tags with relevant content and experts.

Источник

Storing datetime as UTC in PHP/MySQL

Everywhere I read about converting time to a user’s timezone says that the best method is to store a date and time in UTC then just add the user’s timezone offset to this time. How can I store a date in UTC time? I use the MySQL DATETIME field. When adding a new record to MySQL in my PHP code I would use now() to insert into MySQL DATETIME. Would I need to use something different than now() to store UTC time?

If you go this route, don’t forget to account for daylight saving time — i.e. the timezone offset isn’t necessarily constant for a given user’s location.

Yes I have read so many confusing and conflicting things about doing timezones in PHP, it seems to be one the the things php needs to improve

For those who are looking for more info, I link to these fabulous articles: infiniteundo.com/post/25326999628/… and infiniteundo.com/post/25509354022/…

7 Answers 7

Returns the current UTC date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context

Also PHP date_default_timezone_set() is used in PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting functions return the time in his local time.

In truth though I had a hard time getting this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formatted as ‘UTC’ so strtotime transforms it into a local time if you are not careful. I’m curious to hear if someone has a reliable solution for this problem, one that doesn’t break when dates traverse media boundaries (HTTP->PHP->MySQL and MySQL->PHP->HTTP), also considering XML and RSS/Atom.

If my php script gets the UTC time with gmdate() , this returns a string. Wouldn’t this conflict with setting the field type in MySQL to DATETIME ?

I would suggest inserting the date in UTC time zone. This will save you a lot of headaches in the future with daylight saving problems.

INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP()) 

When I query my data I use the following PHP script:

setTimezone(new DateTimeZone('Europe/Istanbul')); echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s'); > ?> 

You can replace the DateTimeZone value with one of the available PHP timezones.

+1 But you’ll want to escape the timezone literal , i.e., .» \U\T\C» to avoid any collisions with current of future format characters.

This great solution deals with a timestamp field, but is there a similarly elegant solution if the table stores a date & time from client side? i.e. when storing dateimtes, the datetime literals come from client side (using client’s tz).

The DateTime constructor accepts a 2nd arg for the timezone. e.g. $date = new DateTime(‘2000-01-01’, new DateTimeZone(‘UTC’)); You can keep a static copy of the UTC timezone object somewhere so you don’t have to keep re-constructing it.

NOW() gives you the time (including the timezone offset) of the system running your database. To get UTC date/time you should use UTC_TIMESTAMP() as described in the MySQL Reference Manual.

Quoting all the answer from above link in case of delete:

To go along with @ypercube’s comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server’s timezone setting with the —default_time_zone option for retrieval. This allows your retrieval to always be in UTC.

By default, the option is ‘SYSTEM’ which is how your system time zone is set (which may or may not be UTC!):

mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP(); +---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2012-09-25 16:28:45 | +---------------------+ 1 row in set (0.00 sec) 

You can set this dynamically:

mysql> SET @@session.time_zone='+00:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | +00:00 | +--------------------+---------------------+ 1 row in set (0.00 sec) 

Or permanently in your my.cnf:

[mysqld] **other variables** default_time_zone='+00:00' 

Restart your server, and you will see the change:

mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | +00:00 | +00:00 | +--------------------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP(); +---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2012-09-25 20:27:50 | +---------------------+ 1 row in set (0.01 sec) 

As @Haluk suggests, you can store the date as a UTC datetime . I’m complementing his answer for situations when the date you want to insert comes from PHP code, and adding some details about how it works :

$pdo = new PDO('mysql:host=mydbname.mysql.db;dbname=mydbname', 'myusername', 'mypassword'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $insertStmt = $pdo->prepare("insert into test (last_modified)" ." values(:last_modified)"); $insertStmt->bindParam(':last_modified', $lastModified, PDO::PARAM_STR); $lastModified = gmdate("Y-m-d H:i:s"); $insertStmt->execute(); 

Actually datetime in PHP doesn’t have a timezone associated to it. What is passed to PDO is just a string, in one of the formats recognized by MySQL, representing the date in UTC timezone. For example if the date is 2015-10-21 19:32:33 UTC+2:00 , the code above just tells MySQL to insert 2015-10-21 17:32:33 . gmtdate(«Y-m-d H:i:s») gives you the current date in such a format. In any case, all you need to do is build the string representing the date you want to insert in UTC time.

Then, when you read the date, you have to tell PHP that the timezone of the date you just retrieved is UTC. Use the code in Haluk’s answer for this.

Источник