PHP / MySQL storage and retrieval time

Question

Like many people, I am completely confused by many date functions in PHP and MySQL. I need to be able to store the date in MySQL and be able to view it on the screen in an easy-to-read format, search on it for months, years or a combination of both using a standard web form or sort them by months or years.

An example of a search is all February entries for the last 5 years.

I have a javascript calendar that introduces a month in a form like 02-12-2011.

What is the best format for this. What should be the field in MySQL.

thanks

Answer 1

Please use a DateTime object.

Save dates in mysql as DATE format.

When writing data

 $date = new DateTime($_POST['date']); or $date = DateTime::createFromFormat('dm-Y', $_POST['date']); $query = sprintf("INSERT INTO `data` SET `date` = '%s'", $date->format('Ym-d')) 

When reading data, create a DateTime object.

 $date = new DateTime($row['date']); 

Then you can print it in any format, for example, javascript format:

 echo $date->format('dm-Y'); 

Cm

http://www.php.net/manual/en/class.datetime.php

and for date formats:

http://www.php.net/manual/en/function.date.php

As for searches, you can use mysql Date functions in fields.

For all entries in February over the past 5 years.

 SELECT * FROM `data` WHERE MONTH(`date`) = 2 AND YEAR(`date`) >= YEAR(NOW()) - 5 

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Answer 2

The column type in MySQL must be date .

Answer 3

it is a date, so save it as a DATE column. You can use UNIX_TIMESTAMP () in your SQL query or strtotime in PHP to convert this back to a value that can be passed to the php date () function to output any format date that you would like.