PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

PHP-MYSQL: Converting Unix Timestamp to DateTime and vice versa

When working with databases in PHP, you frequently encounter a mismatch between how MySQL stores time and how your application interprets it. A common scenario involves storing DATETIME values in the database but needing to send numeric Unix timestamps to a client for sorting or filtering. The confusion usually stems from an invisible culprit: timezone handling.

Consider a setup where you are using PHP 5.4.6 and MySQL 5.5.29 on the same local machine. You have a table like this:

CREATE TABLE Entry(
    id SERIAL PRIMARY KEY,
    created DATETIME NOT NULL
);

You insert records using NOW() via PDO, which correctly stores the current server time. For example, a record might store 2013-02-14 20:47:35. To deliver this as a Unix timestamp to your client, you query the database directly:

SELECT created, UNIX_TIMESTAMP(created) AS TS FROM Entry

This returns a consistent integer, say 1360871255. The logic here seems sound; MySQL converts the stored datetime to UTC-based seconds since the epoch.

The problem arises when you attempt the reverse operation in PHP. You receive 1360871255 and expect to reconstruct 2013-02-14 20:47:35. You might write a helper function like this:

public static function toDateTime($unixTimestamp){
    return date("Y-m-d H:m:s", $unixTimestamp);
}

When you call toDateTime(1360871255), the output is 2013-02-14 20:02:35. The minutes and seconds are off, and even if the time looked close, relying on string formatting for logic is a bad practice. More critically, if you try to use that number directly in a comparison query like WHERE created < 1360871255, the results will not match your expectations because the numeric value represents UTC, while your database column might be stored in a different timezone or interpreted differently depending on the connection settings.

The root cause is almost always the default timezone configuration of the PHP environment versus the server's system timezone. The date() function in PHP does not automatically assume UTC; it defaults to the timezone set in your php.ini file, which is often the local time zone (e.g., America/New_York or Europe/London). MySQL's UNIX_TIMESTAMP() function, however, always calculates seconds relative to UTC.

When you convert a local datetime string to a timestamp in PHP using strtotime() or similar functions without specifying a timezone, PHP assumes your system's local time. If your server is in UTC+0 (or another offset), the math works. But if your server is in UTC-5, and you treat that local time as UTC, you end up shifting the time by several hours.

To fix this, you must ensure consistency. The most robust approach is to force PHP to operate in UTC when dealing with raw Unix timestamps. You can do this by setting the default timezone explicitly:

date_default_timezone_set('UTC');

Once the timezone is set to UTC, your conversion logic will align perfectly with MySQL's UNIX_TIMESTAMP(). Your function becomes reliable:

public static function toDateTime($unixTimestamp){
    return date("Y-m-d H:i:s", $unixTimestamp);
}

With this change, passing 1360871255 will correctly yield 2013-02-14 20:47:35, matching the value stored in the database. This allows you to safely construct queries like SELECT * FROM Entry WHERE created < 1360871255 because both sides of the comparison are now interpreting the time values in the same frame of reference.

It is also worth noting that while DATETIME columns store time without timezone information, TIMESTAMP columns automatically convert between UTC and the server's session timezone. If you switch your column type to TIMESTAMP, you must ensure your application consistently uses the same timezone settings to avoid drift. For most applications handling raw epoch seconds, sticking with DATETIME and enforcing UTC in PHP logic is the cleaner solution.

For developers using modern frameworks like Laravel, this issue is abstracted away by default configuration, but understanding the underlying mechanics helps when debugging custom scripts or migrations. Always verify your environment's timezone settings before assuming that a numeric timestamp represents a specific moment in time.

Our network