Getting a PHP DateTime object into a string representation of a MYSQL date

Question

I have a DateTime which I want to store in a Date MySQL column. I am using MySQLi and prepared statements.

When binding parameters, I cannot specify date as a type, only strings and integers.

How do I convert the DateTime to a MySQL date string representation? There is very little documentation on the DateTime class.

Answer 1

I almost closed this as a duplicate of Convert from MySQL datetime to another format with PHP but I think it's actually the reverse problem. That question asked how to format a date fetched from MySQL, and you're asking how to format a date for input to a query.

Option 1:

SELECT * FROM MyTable WHERE DateTimeCol = ?

You can format the value to the YYYY-MM-DD HH:MM format MySQL wants:

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

Then submit it as a string parameter to the query.

Option 2:

SELECT * FROM MyTable WHERE DateTimeCol = FROM_UNIXTIME(?)

Then you can submit the timestamp value as a numeric parameter to the query.

Option 3:

SELECT * FROM MyTable WHERE DateTimeCol = STR_TO_DATE(?, '%m/%d/%y')

You can submit a wide variety of string representations of date/time, if you specify the formatting to MySQL's STR_TO_DATE() function. For the formatting codes, see the DATE_FORMAT() function.

Answer 2

Because the DateTime-class introduced with PHP5 is poorly documented, i wouldn't recommend to use it. I find regular timestamps to be much easier to work with!

But if you still want to use DateTime-objects, a solution would be to transform the objects into (unix) timestamps before storing them in your database. Transform the timestamps back to DateTime-objects after reading information from your database.

To create DateTime-object from a timestamp, use date_create() with the timestamp as argument. To get your objects representation of number of seconds since the Unix Epoch, use date_format($object, 'U') (uppercase U).