Formatting an SQL timestamp with PHP

Formatting an SQL timestamp with PHP

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

Stefan Bogdanescu

Founder & Senior Architect

Formatting an SQL Timestamp with PHP

You are seeing 12-31-69 because your code is treating the database value as a Unix timestamp (seconds since January 1, 1970) rather than a formatted date string. In MySQL, the timestamp data type stores values internally as integers representing Unix time, but when you retrieve them via standard SQL queries, they are often returned as strings in the format YYYY-MM-DD HH:MM:SS.

When PHP's date() function receives an integer that looks like a timestamp (e.g., 1234567890), it interprets it correctly. However, when you pass a string like "2010-02-20 13:14:09" directly into date(), PHP attempts to parse that string as a Unix timestamp. Since "2010-02-20 13:14:09" is not a valid integer, PHP treats it as 0. Consequently, date() defaults to the epoch start date, which is January 1, 1970. Depending on your server's timezone configuration, this often renders as December 31, 1969, or similar artifacts in older PHP versions or specific environments.

To fix this, you must ensure the value passed to date() is either a valid Unix timestamp integer or a string that strtotime() can successfully parse into an internal timestamp representation.

Parsing the String Value

If your query returns the date as a string (e.g., '2010-02-20 13:14:09'), you need to convert it before formatting. The most robust approach is using strtotime():

// Assuming $r['newsDate'] contains '2010-02-20 13:14:09'
echo date("m-d-Y", strtotime($r['newsDate']));

strtotime() accepts a wide variety of date formats, including the ISO standard used by MySQL. It converts the string into a Unix timestamp integer, which date() can then format reliably. This method handles edge cases well and is generally safer than trying to parse the string manually.

Returning Timestamps from SQL

If you control the database query, you might consider having MySQL return the raw integer directly. However, this requires careful handling because MySQL's internal timestamp storage differs slightly from a standard INT.

In older versions of PHP (pre-5.3), there was a specific incompatibility where fetching a TIMESTAMP column returned a numeric value that didn't match the Unix epoch count exactly, leading to the exact issue you encountered. In modern PHP versions (5.3+ and 7.x/8.x), this is largely handled automatically if you use prepared statements with PDO or MySQLi in their default modes.

If you are using PDO:

$stmt = $pdo->prepare("SELECT newsDate FROM news WHERE id = ?");
$stmt->execute([$id]);
$row = $stmt->fetch();

// Ensure we handle the type correctly
$dateString = $row['newsDate']; // Usually a string 'YYYY-MM-DD HH:MM:SS'

echo date("m-d-Y", strtotime($dateString));

Formatting Options and Trade-offs

Once you have a valid timestamp, you can format it however you prefer. Your original attempt used "m-d-Y", which produces 02-20-2010 (month-day-year). Common alternatives include:

  • "Y-m-d" for 2010-02-20 (ISO 8601, database-friendly)
  • "F j, Y" for February 20, 2010 (human-readable)
  • "M d, Y g:i A" for Feb 20, 2010 01:14 PM

When choosing a format, consider your downstream consumers. If the data is being displayed to end-users, a human-readable format like "F j, Y" improves readability. If the data is being processed by other scripts or stored in logs, ISO 8601 ("Y-m-d H:i:s") is preferred for consistency and sorting accuracy.

Avoiding Common Pitfalls

A frequent mistake is assuming that a timestamp column always returns an integer. In MySQL, unless you explicitly select it as a number or use specific flags, it often returns a string. Relying on implicit type conversion can lead to silent failures, especially when mixing PHP versions or database drivers.

Another pitfall is timezone mismatches. If your server's default timezone differs from the database connection's timezone, the interpreted time might shift by hours before formatting. Always ensure date_default_timezone_set() is configured appropriately if you are working with user-facing times.

By converting the string to a timestamp using strtotime() or ensuring your query returns a valid integer, you eliminate the epoch default behavior and restore correct date formatting in your application.

Our network