PHP date() format when inserting into datetime in MySQL

Question

What is the correct format to pass to the date() function in PHP if I want to insert the result into a MySQL datetime type column?

I've been trying date('Y-M-D G:i:s') but that just inserts "0000-00-00 00:00:00" everytime.

Answer 1

The problem is that you're using 'M' and 'D', which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13

Try: date('Y-m-d H:i:s') which uses the numeric equivalents.

edit: switched G to H, though it may not have impact, you probably want to use 24-hour format with leading 0s.

Answer 2

From the comments of php's date() manual page:

<?php $mysqltime = date ('Y-m-d H:i:s', $phptime); ?>

You had the 'Y' correct - that's a full year, but 'M' is a three character month, while 'm' is a two digit month. Same issue with 'D' instead of 'd'. 'G' is a 1 or 2 digit hour, where 'H' always has a leading 0 when needed.

Answer 3

Here's an alternative solution: if you have the date in PHP as a timestamp, bypass handling it with PHP and let the DB take care of transforming it by using the FROM_UNIXTIME function.

mysql> insert into a_table values(FROM_UNIXTIME(1231634282));
Query OK, 1 row affected (0.00 sec)

mysql> select * from a_table;

+---------------------+
| a_date              |
+---------------------+
| 2009-01-10 18:38:02 |
+---------------------+

Answer 4

I use the following PHP code to create a variable that I insert into a MySQL DATETIME column.

$datetime = date_create()->format('Y-m-d H:i:s');

This will hold the server's current Date and Time.

Answer 5

$date_old = '23-5-2016 23:15:23'; 
//Date for database
$date_for_database = date ('Y-m-d H:i:s'", strtotime($date_old));

//Format should be like 'Y-m-d H:i:s'`enter code here`

Answer 6

I use this function (PHP 7)

function getDateForDatabase(string $date): string {
    $timestamp = strtotime($date);
    $date_formated = date('Y-m-d H:i:s', $timestamp);
    return $date_formated;
}

Older versions of PHP (PHP < 7)

function getDateForDatabase($date) {
    $timestamp = strtotime($date);
    $date_formated = date('Y-m-d H:i:s', $timestamp);
    return $date_formated;
}

Answer 7

Format time stamp to MySQL DATETIME column :

strftime('%Y-%m-%d %H:%M:%S',$timestamp);

Answer 8

Format MySQL datetime with PHP

$date = "'".date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $_POST['date'])))."'";

Answer 9

There is no need no use the date() method from PHP if you don't use a timestamp. If dateposted is a datetime column, you can insert the current date like this:

$db->query("INSERT INTO table (dateposted) VALUES (now())");

Answer 10

This has been driving me mad looking for a simple answer. Finally I made this function that seems to catch all input and give a good SQL string that is correct or at least valid and checkable. If it's 1999-12-31 it's probably wrong but won't throw a bad error in MySQL.

function MakeSQLDate($date) {
    if (is_null($date)) {
        //use 1999-12-31 as a valid date or as an alert
        return date('Y-m-d', strtotime('1999-12-31'));
    }

    if (($t = strtotime($date)) === false) {
        //use 1999-12-31 as a valid date or as an alert
        return date('Y-m-d', strtotime('1999-12-31'));
    } else {
        return date('Y-m-d H:i:s', strtotime($date));
    }
}

Answer 11

Using DateTime class in PHP7+:

function getMysqlDatetimeFromDate(int $day, int $month, int $year): string
{
 $dt = new DateTime();
 $dt->setDate($year, $month, $day);
 $dt->setTime(0, 0, 0, 0); // set time to midnight

 return $dt->format('Y-m-d H:i:s');
}

Answer 12

This is a more accurate way to do it. It places decimals behind the seconds giving more precision.

$now = date('Y-m-d\TH:i:s.uP', time());

Notice the .uP.

More info: https://stackoverflow.com/a/6153162/8662476

Answer 13

A small addendum to accepted answer: If database datetime is stored as UTC (what I always do), you should use gmdate('Y-m-d H:i:s') instead of date("Y-m-d H:i:s").

Or, if you prefer to let MySQL handle everything, as some answers suggest, I would insert MySQL's UTC_TIMESTAMP, with the same result.

Note: I understood the question referring to current time.