3

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • Almost closed this as a duplicate question, then I realized it was different and I reopened it. Please excuse my haste! – Bill Karwin Dec 28 '08 at 20:24

2 Answers2

3

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Second are optional, but yes that works. If you use 'H' (12-hour clock) instead of 'G' (24-hours clock), then you should also include AM/PM. Refer to DATE_FORMAT() documentation for full list of codes. – Bill Karwin Dec 29 '08 at 18:33
  • 'h' is 12-hour clock, 'H' is 24 with leading zeros and 'G' is 24 without leading zeros. You should indeed use 'H'. – Christian Studer Feb 02 '09 at 13:46
  • I've edited the format string in option 1 above with respect to these comments from ʞɔıu and christian studer. – Bill Karwin Feb 02 '09 at 17:07
1

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).

qualbeen
  • 1,534
  • 4
  • 16
  • 27