413

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.

2
  • 3
    since you're not supplying a parameter to date do you actually want to record the current time? Commented Feb 7, 2010 at 0:28
  • I know this question is 12 yo. But nowadays it would be beneficial to use \DateTime and the convertion functions it offers Commented Apr 18, 2022 at 14:32

15 Answers 15

839

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.

Sign up to request clarification or add additional context in comments.

11 Comments

this has to be one of the single most common datetime formats anyone would ever need. should be a built in php global or part of the date function itself (e.g., 'c', 'r')
Why this is not the case I do not know. I have to refer to stack overflow every. single. functioning. time.
It's faster to come here, than search in my code for a usage like this.
Just thought of a cute mnemonic for remembering the format: "Your my date not His ". works on so many levels :)
There should be a php shorthand for this as there is for ISO 8601 and RFC 2822
|
128

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.

2 Comments

Thanks @Pekka - I remember when you and I had roughly the same rep - you've been pretty active.
yeah, I'm working a lot right now, and SO is my favourite pastime in between :) that will change again.
44

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 |
+---------------------+

6 Comments

That's the best option, if you can work on timestamps. Let super-fast DB do everything, it can, instead of waiting for super-slow PHP!
@trejder, Who told you that doing it in mysql is faster than doing it in php? Doing it in PHP is better because the DB usually is the bottleneck and you want to get in and out asap.
@Pacerier the DB is the bottleneck if you have a poorly built DB, or are running too many small queries instead of a more robust effective single query, or in a lot of cases if you rely on an ORM to manage the database. Solid SQL with a well indexed relational database is not slow.
@mopsyd, The DB is the bottleneck because scaling it horizontally requires complexity. Compare that to webservers that can be duplicated across the globe without any code changes.
If the query fails, is logged somewhere, and you have to debug it, then the timestamp is much harder to read than the formatted date string described by @tim-lytle.
|
33

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.

Comments

16
$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`

1 Comment

Although this code may answer the question, providing additional context regarding why and/or how it answers the question would significantly improve its long-term value. Please edit your answer to add some explanation, and to fix your formatting problems.
12

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;
}

2 Comments

I've voted up your answer because it worked after I edited the first line; function getDateForDatabase(string $date) : string { to function getDateForDatabase($date) { Why the type declarations throw an error I don't know enough PHP to tell.
"Argument 1 passed to getDateForDatabase() must be an instance of string, string given," - my input was a string,
9

Format time stamp to MySQL DATETIME column :

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

Comments

8

Format MySQL datetime with PHP

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

Comments

8

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())");

1 Comment

Thanks for share this approach. It the easiest method I have seen until now.
5

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.

5 Comments

Good answer. All the other answers seem to miss the subtle issues surrounding the timezone. I mean, how do you know your SQL server's timezone, or your webserver's, or the user? Bare calls to date() or NOW() seem like timezone bug magnets.
@ChrisNadovich, mysql's timezone can be displayed with "SELECT @@global.time_zone;". Default is "SYSTEM", server's TZ. "0:00" is UTC. For apache, you may use SSI's '<!--echo var="DATE_LOCAL" -->'. It may different for PHP. For a user, "date +%Z" in a terminal (but any process may override it).
Well, of course, @bruno. Nevertheless, not talking about timezones at all seems to be a flaw in the "preferred" answers here. I mean, wouldn't it be nice if we could use 'Y-m-d H:i:s e' and have SQL automatically store an unambiguous time from consideration of that 'e' as it automatically deals with the Y, m, d, H, i, and s.
Yes @ChrisNadovich For me, the only non-ambiguous system is to have the server using UTC. And then making conversions on client side, for user usage only. Any different setting on server imply we cannot guarantee the relative order of two records (think about a record inserted 1m before winter/summer time change, a second one 1mn later, with the server clock going backwards 1h in-between).
UTC_TIMESTAMP for a PHP script is the safest and fastest way, less code, a simple insert or update with that MySQL timestamp
5

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');
}

1 Comment

I can only recommend the use of \DateTime. It can construct a PHP date directly from a MySQL string and observes the timezone your server app is working in.
3

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

Comments

1

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));
    }
}

2 Comments

If you're having to check for null then something is very wrong with your upstream data!
Nullable date columns are not necessarily an invalid pattern. Consider an application with a model that has an "expiration" date. If an instance sometimes has no expiration (i.e, infinitely valid), storing its lack of expiration as "NULL" in the database is simpler than requiring JOINs for every other expiring record.
0

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.

Comments

0

I need Y-m-d this format of date in excel export. This is my code.

$contents .= "<td>". date('Y-m-d', strtotime($row3['DATED'])) . "</td>";

And these are headers

header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=Log_Export_Alico_CSV_" . date('Y-m-d') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

// Output CSV content
echo "\xEF\xBB\xBF"; // UTF-8 BOM
echo $contents;
exit();

When I export the date is still showing m-d-Y.

When I use

$contents = strip_tags($contents);

with this the date format is exported as my desire but the whole format of export data is disturbed.

Please advice me to resolve this problem.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.