Convert from MySQL datetime to another format with PHP

Convert from MySQL datetime to another format with PHP

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

Stefan Bogdanescu

Founder & Senior Architect

Converting MySQL datetime to US Format in PHP

When working with database records, you often need to present timestamps in a human-readable format that differs from the raw YYYY-MM-DD HH:MM:SS output returned by MySQL. Specifically, converting a column to the American "mm/dd/yy H:M (AM/PM)" style requires handling date parsing and formatting logic within your application layer. Since PHP provides robust tools for this via the DateTime and DateInterval classes (and their modern replacements in PHP 8), you can handle this transformation efficiently without relying on raw SQL string manipulation, which often leads to data type inconsistencies.

The most direct approach involves fetching the datetime column as a standard string or timestamp from the database and processing it in PHP. When you retrieve a DATETIME column using PDO or MySQLi, the driver typically returns a string like "2023-10-25 14:30:00". You can initialize a DateTime object with this string, which normalizes the data regardless of the specific time zone settings on your server.

$dbTime = "2023-10-25 14:30:00"; // Example value from database
$dateObj = new DateTime($dbTime);

Once the object is created, you apply a custom format string to match your requirements. The target format mm/dd/yy H:M (AM/PM) translates directly to PHP's format() constants or their character equivalents: 'm' for the month (01-12), 'd' for the day (01-31), 'y' for the 2-digit year, 'H' for hours in 24-hour format (00-23), and 'i' for minutes. To achieve the AM/PM suffix, you must use the special character a, which formats the time as "am" or "pm" in lowercase.

$formattedDate = $dateObj->format('m/d/y H:i a');
// Output: 10/25/23 2:30 pm

However, there is a critical nuance regarding the H versus h modifier and the hour representation. If your requirement strictly dictates single-digit hours (e.g., "2:30" instead of "02:30"), you must use the h format character for 12-hour time. The example above uses i for minutes, which always includes a leading zero (e.g., "05"). If you need single-digit minutes as well, swap i for i but ensure your logic handles the specific minute width required; strictly speaking, PHP's standard formatter always pads minutes with a zero. To get non-padded minutes, you would need to manually strip the leading zero using str_replace('0', '', ...) or custom formatting logic.

// Standard: 10/25/23 14:30:00 -> 10/25/23 2:30 pm
$formattedDate = $dateObj->format('m/d/y h:i a');

A common pitfall arises when handling time zones. MySQL stores DATETIME values without time zone information, whereas TIMESTAMP columns do store them relative to the server's time zone. If you query a TIMESTAMP column and your server is in UTC while your application expects local time, simply formatting the raw string might display the wrong hour. You should explicitly set the timezone on your DateTime object before formatting if the data represents local time but the database is in UTC:

$dateObj->setTimezone(new DateTimeZone('America/New_York'));
$formattedDate = $dateObj->format('m/d/y h:i a');

While it is tempting to perform this conversion directly in the SQL query using DATE_FORMAT, doing so can introduce compatibility issues across different MySQL versions and makes it harder to handle time zone logic dynamically. Handling the formatting in PHP ensures consistency with the rest of your application stack and allows for easier localization if you decide to support multiple regions later. For developers working within the Laravel ecosystem, this logic is easily integrated into Eloquent accessors or global date helpers, ensuring a consistent user experience across all API endpoints.

Our network