MySQL and PHP working with user timezones

Question

I store time in UTC timezone in MySQL DATETIME field. I want to convert all these (when using them in PHP) to user defined timezone. I know I can do it with php DateTime::setTimezone, but it would require to do it each for each one of them.

Is there some global way to tell php that all datetimes from mysql are UTC and convert them automatically to the user timezone?

I have asked these questions recently. see this one and more — xkeshav
– xkeshav, Commented Jul 10, 2012 at 12:18
Added a PHP function to my answer that will resolve a UTC time to the user time. Just call it when you get the data back from the database. — Fluffeh
– Fluffeh, Commented Jul 10, 2012 at 13:24

Community · Accepted Answer · 2017-05-23 12:07:49Z

3

When you store them into the database, store the table column as a unix timestamp. These are all UTC. Then just apply whatever user specified offset you want.

Here is an interesting StackOverflow question which is likely to help you with it.

You can also use the MySQL Unix_Timestamp function to query data easily by generating the right int when doing comparisons.

Edit: If you are certain that you want to convert all the datetimes from the database each time you use them, just write a simple function that gets the datetime and adds the correct offset for the user, then simply call that function each time you pluck a datetime from the database. like the code below:

<?php

    // Assuming that $_SESSION['UserOffset'] is storing the user's timezone eg
    // $_SESSION['UserOffset'] = new DateTimeZone("Europe/Prague");

    function convert_time_zone($timeFromDatabase_time)
    {
        $userTime = new DateTime($timeFromDatabase, new DateTimeZone('UTC'));
        $userTime->setTimezone(new DateTimeZone($_SESSION['UserOffset']));
        return $userTime->format('Y-m-d H:i:s');
        // or return $userTime; // if you want to return a DateTime object.
    }


?>

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Jul 10, 2012 at 12:17

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin Petercak Over a year ago

What if I want to use datetime column (it has some benefits over timestamp)?

feeela Over a year ago

Which also needs to be done on each call and thus isn't much less work in comparison to using the DateTime() objects. Also timestamps are not GMT but UTC. Additionally timestamps couldn't store historic dates and debugging is harder, because most people can't read a timestamp like a normal date/time notation.

Junaid Over a year ago

How would php gets the offset of the timezone?

Fluffeh Over a year ago

You either get the user to set their timezone (and store in a session or cookie) or use some ajax, there are a number of examples online to do it. However you get it, once you have it, it is easy enough to get PHP to do the work to convert the output.

Martin Petercak Over a year ago

Yep, I went with something like this. I'm using a database layer and I modified it to do new DateTime($date, new DateTimeZone('UTC')); when the column type is datetime. And i use date_default_timezone_set($userTimeZone). And everything is alright.

Collectives™ on Stack Overflow

MySQL and PHP working with user timezones

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related