HOWTO · PHP
Date Format in PHP for Insertion in MySQL
This article will introduce how to format date in PHP, before inserting it to MySQL.
On this page
PHP can format a date or time as a string that matches a MySQL temporal column. The format string determines which parts are included; it does not save a row. Choose a column for the meaning of the value, format it consistently, then pass it as a bound value in a prepared statement.
Choose the MySQL date or time type
Choose the column according to what the value represents:
DATEstores a calendar date such as2026-11-05, without a time of day.DATETIMEstores a date and time such as2026-11-05 14:30:00as a calendar value.TIMESTAMPstores a date and time with MySQL’s session-time-zone conversion behavior. Use it when that behavior matches the application; coordinate the connection timezone with the application.TIMEstores a time value or duration, such as14:30:00.YEARstores a year, such as2026.
For a date-only column, send YYYY-MM-DD. For a date-and-time column, send YYYY-MM-DD HH:MM:SS. The examples below use these common MySQL representations. Check the target column and server configuration when handling fractional seconds, special values, or values outside your application’s expected range.
Format a date with PHP’s date()
PHP’s date() returns a formatted string. Its first argument is a format pattern; an optional Unix timestamp is the second argument. If you omit the timestamp, PHP uses the current time, interpreted with PHP’s configured default timezone.
In PHP format patterns, Y is a four-digit year, m a zero-padded month, d a zero-padded day, H a 24-hour hour, i minutes, and s seconds. Uppercase Y differs from lowercase y, which gives a two-digit year. The lowercase i means minutes; m means month.
Run this example:
<?php
date_default_timezone_set('UTC');
$timestamp = strtotime('2026-11-05 14:30:24 UTC');
$dateValue = date('Y-m-d', $timestamp);
$dateTimeValue = date('Y-m-d H:i:s', $timestamp);
echo $dateValue, PHP_EOL;
echo $dateTimeValue, PHP_EOL;
?>
Use Y-m-d for a DATE value and Y-m-d H:i:s for a whole-second date and time. Set or otherwise control the timezone intentionally when the formatted value represents a local time; do not let a server’s implicit default decide the meaning of stored data.
Format a DateTime object with date_format()
date_format() takes a DateTime object and a format pattern, and returns a string. It is equivalent to calling the object’s format() method. Creating the object with an explicit timezone makes the intended interpretation clear. Formatting still does not insert a database row.
Create the DateTime with an explicit timezone, then pass the object and a pattern to date_format(). This example formats a known UTC instant as a date and time:
<?php
$date = new DateTime('2026-11-05 14:30:24', new DateTimeZone('UTC'));
$formatted = date_format($date, 'Y-m-d H:i:s');
echo $formatted;
?>
The result is the string 2026-11-05 14:30:24; formatting returns a string and does not insert a database row. Use date_format($date, 'Y-m-d') for a date-only column. The same tokens apply: Y, m, and d select the calendar date; H, i, and s add the time. If you construct a DateTime from user input, validate that input and select the timezone before formatting.
Insert the formatted value safely
Formatting produces a string; a prepared statement sends that string to MySQL as a parameter. Keep the SQL structure fixed and bind the value instead of concatenating it into the SQL text. For PDO, the core pattern is $statement = $pdo->prepare('INSERT INTO events (starts_at) VALUES (:starts_at)');, followed by $statement->execute(['starts_at' => $dateTimeValue]);. Use a column name that exists in your schema and create $pdo with your application’s database connection settings. The database connection and insert require a configured MySQL service, so this pattern is illustrative rather than a standalone runnable example.
For a DATE column, bind the Y-m-d string; for a DATETIME column, bind the corresponding date and time string. The placeholder represents a value, not an identifier, so table and column names must come from trusted application code. Parameter binding protects the value from being interpreted as SQL syntax; it does not validate whether the chosen date is meaningful for the application.
Check the value and timezone
Before inserting, confirm that the PHP pattern matches the column: Y-m-d for a calendar date, Y-m-d H:i:s for a date and time, and H:i:s for a time value. A common mistake is using m for minutes; PHP uses m for month and i for minutes. In MySQL DATE_FORMAT(), %m is the month specifier and %i is the minute specifier. In the common YYYY-MM-DD HH:MM:SS representation, the positions distinguish the month from the minutes; MI is not a MySQL minute token.
Decide whether the application works in UTC or a named local timezone, and apply that choice consistently when creating or formatting the value. For TIMESTAMP, also account for the MySQL session timezone conversion; DATETIME does not provide the same conversion semantics. If the stored value is unexpected, inspect the original input, the PHP timezone, the exact formatted string, the column type, and the MySQL connection’s session timezone. Keep the formatted string visible in logs only when it is appropriate for your application’s privacy policy.