PHP的MySQL日期字段可选月或日 [英] Optional month or day in MySQL date field from PHP
问题描述
我有一个问题，我需要处理月份和日期是可选的日期。例如，年份将永远是知道的，但有时候日或月和日将是未知的。
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.
在MySQL中，我可以创建一个带有日期字段的表，而我在MySQL手册中找不到任何引用，它将接受以下内容：
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
从数据库内测试计算工作正常，所以我仍然可以轻松排序结果。在手册中说，这个月需要在01到12之间，而在01到31之间的日子，但是它接受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.
第一个问题：我要去遇到麻烦使用00在这个月份或日子的部分或者这是完全可以接受的？
First question: Am I going to run into trouble using 00 in the month or day parts or is this perfectly acceptable?
下一个问题：是否有一个PHP函数（或MySQL格式的命令），将自动格式化以下日期为必填格式字符串？
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?
以下内容不起作用：
<?php
$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
?>
第三个问题：是否有PHP函数（或MySQL命令）来格式化PHP中使用的日期？
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?
编辑：
这是我目前正在做的：
日期字段可以接受格式为YYYY，YYYY-MM或YYYY-MM-DD的日期，在发送到数据库之前，它将在此功能中处理：
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<year>\d{4})[- _/.]?(?P<month>\d{0,2})[- _/.]?(?P<day>\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;
}
所以它的模式匹配输入$ phpDate，它必须以4位数开始，然后选择月份和日期的数字组合。这些存储在一个名为$ parts的数组中。
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.
最后，如果一切都检出，它返回一个有效的日期以及一个格式的字符串。否则它会返回FALSE。
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.
在许多情况下，我们确实需要或多或少的精确日期，我使用这样的日期，如 2011-04-01 （精确），以及<档案元数据中的code> 2011-04 （= 2011年4月）和 2011 （仅年份）。正如你所说，MySQL日期字段容忍2011-00-00，虽然没有常见问题解答，但是很好。
但是，我不得不将 MySQL数据库通过 ODBC 和日期字段
正确翻译，除了容忍日期（例如：2011-04-00结果在结果MySQL-ODBC连接的ACCESS数据库中空。
因此，我得出结论，MySQL日期字段可以在一个简单的 VARCHAR（10）中转换， 字段：只要我们不需要特定的MySQL日期函数，它的工作正常，当然我们仍然可以使用php date函数和你的fine_php2mysql（）函数。
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.
我会说唯一需要MySQL日期字段的情况
是当需要复杂的SQL查询时，使用 MySQL （但是这样的查询在或多或少的精确日期不再工作！...）
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!...)
结论：对于或多或少精确的日期，
我现在丢弃MySQL日期字段，并使用简单的 VARCHAR（10）字段
与 aaaa-mm-jj 格式数据。简单美丽。
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.
这篇关于PHP的MySQL日期字段可选月或日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
