Darren O'Neill

Timestamps and databases

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:


/* 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);

/* 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.

    id serial NOT NULL PRIMARY KEY,
    processed boolean NOT NULL DEFAULT false,
    "dateSent" timestamp without time zone DEFAULT timezone('UTC'::text, now())