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

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 11

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.

Answer 12

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 13

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 14

IMO in addition to the date() function options provided in the previous answers, you should carefully consider its use the server time zone setting which may be different from the database one. If the UTC time zone is needed then the gmdate() function (it has the same options of date()) will be more convenient for the specific case.