php mysql 时间字段,PHP的MySQL日期字段可选月或日

I have a problem where I need to handle dates where the month and day parts are optional. For example, the year will always be known but sometimes the day or month and day will be unknown.

In MySQL I can create a table with a date field and while I can't find any reference in the MySQL Manual it will accept the following as valid:

(YYYY-MM-DD format):

2011-02-10 // Current date

2011-02-00 // Day unknown so replaced with 00

2011-00-00 // Day and month unkown so replaced with 00-00

Test calculations from within the database work fine so I can still sort results easily. In the manual it says that month needs to be between 01 and 12, and day between 01 and 31 - but it does accept 00.

First question: Am I going to run into trouble using 00 in the month or day parts or is this perfectly acceptable?

Next question: Is there a PHP function (or MySQL format command) that will automatically format the following dates into the required format string?

2011 becomes 2011-00-00

2011-02 becomes 2011-02-00

Or do I need write a special function to handle this?

The following doesn't work:

$date = date_create_from_format('Y-m-d', '2011-00-00');

echo date_format($date, 'Y-m-d');

// Returns 2010-11-30

$date = date_create_from_format('Y-m-d', '2011-02-00');

echo date_format($date, 'Y-m-d');

// Returns 2011-01-31

?>

Third question: Is there a PHP function (or MySQL command) to format the dates for use in PHP?

Finally, is this the best approach? Or is there a 'best practise' method?

EDIT:

Here is what I'm currently doing:

A date field can accept a date in the format YYYY, YYYY-MM, or YYYY-MM-DD and before sending to the database it is processed in this function:

/**

* Takes a date string in the form:

* YYYY or

* YYYY-MM or

* YYYY-MM-DD

* and validates it

*

* Use date_format($date, $format); to reverse.

*

* @param string $phpDate Date format [YYYY | YYYY-MM | YYYY-MM-DD]

*

* @return array 'date' as YYYY-MM-DD, 'format' as ['Y' | 'Y-m' | 'Y-m-d'] or returns false if invalid

*/

function date_php2mysql($phpDate) {

$dateArr = false;

// Pattern match

if (preg_match('%^(?P\d{4})[- _/.]?(?P\d{0,2})[- _/.]?(?P\d{0,2})%im', trim($phpDate), $parts)) {

if (empty($parts['month'])) {

// Only year valid

$date = $parts['year']."-01-01";

$format = "Y";

} elseif (empty($parts['day'])) {

// Year and month valid

$date = $parts['year']."-".$parts['month']."-01";

$format = "Y-m";

} else {

// Year month and day valid

$date = $parts['year']."-".$parts['month']."-".$parts['day'];

$format = "Y-m-d";

}

// Double check that it is a valid date

if (strtotime($date)) {

// Valid date and format

$dateArr = array('date' => $date, 'format' => $format);

}

} else {

// Didn't match

// Maybe it is still a valid date

if (($timestamp = strtotime($phpDate)) !== false) {

$dateArr = array('date' => date('Y-m-d', $timestamp), 'format' => "Y-m-d");

}

}

// Return result

return $dateArr;

}

So it pattern matches the input $phpDate where it must begin with 4 digits, then optionally pairs of digits for the month and the day. These are stored in an array called $parts.

It then checks if months or days exist, specifying the format string and creating the date.

Finally, if everything checks out, it returns a valid date as well as a format string. Otherwise it returns FALSE.

I end up with a valid date format for my database and I have a way of using it again when it comes back out.

Anyone think of a better way to do this?

解决方案I have a problem where I need to handle dates where the month and day parts are optional.

For example, the year will always be known but sometimes the day or month and day will be

unknown.

In many occasions, we do need such 'more or less precise' dates, and I use such dates as 2011-04-01 (precise), as well as 2011-04 (= April 2011) and 2011 (year-only date) in archives metadata. As you mention it, MySQL date field tolerates '2011-00-00' though no FAQs tell about it, and it's fine.

But then, I had to interface the MySQL database via ODBC and the date fields

are correctly translated, except the 'tolerated' dates (Ex: '2011-04-00' results empty in the resulting MySQL-ODBC-connected ACCESS database.

For that reason, I came to the conclusion that the MySQL date field could be converted in a plain VARCHAR(10) field : As long as we don't need specific MySQL date functions, it works fine, and of course, we can still use php date functions and your fine date_php2mysql() function.

I would say that the only case when a MySQL date field is needed

is when one needs complex SQL queries, using MySQL date functions in the query itself.

(But such queries would not work anymore on 'more or less precise' dates!...)

Conclusion : For 'more or less precise' dates,

I presently discard MySQL date field and use plain VARCHAR(10) field

with aaaa-mm-jj formated data. Simple is beautiful.