I have found the most reliable way to store times with different time zones is to not actually use a time zone aware type your database may provide. For example PostgreSQL provides timestamp with time zone. The best way is to store everything in UTC time in a naive timestamp type. Then convert in code to your user's time zone.
In PHP you might do something like:
<?php /* Save to the database as UTC time */ $dateTime = new \DateTime("now", new \DateTimeZone('UTC')); $utcTime = $dateTime->format('Y-m-d H:i:s'); $sth = $dbh->prepare( 'UPDATE "myTable" SET processed = true WHERE "dateSent" = :utcTime' ); $sth->bindParam(':utcTime', $utcTime, PDO::PARAM_STR); $sth->execute(); /* Retrieve from the database and convert to local time */ // $dbTimestamp = "2012-08-27 18:33"; $timeFromDatabase = new \DateTime($dbTimestamp, new \DateTimeZone('UTC')); $timeFromDatabase->setTimeZone(new \DateTimeZone('Europe/London')); $localTime = $timeFromDatabase->format('Y-m-d H:i:s');
It is worth noting that in PostgreSQL you can set the default value of a timestamp column to the current time in UTC. This will save you having to set it in code before inserting a new record into the database.
CREATE TABLE "myTable" ( id serial NOT NULL PRIMARY KEY, processed boolean NOT NULL DEFAULT false, "dateSent" timestamp without time zone DEFAULT timezone('UTC'::text, now()) )