MySQL：如何检查字符串是否是有效的DATE，TIME或DATETIME

When I try to put a value into a DATE field which is invalid, MySQL seems to use 0000-00-00 instead. Is there a way I can do this "check" without updating a DATE field? And to do it from for example PHP?

当我尝试将值放入无效的DATE字段时，MySQL似乎使用了0000-00-00。有没有办法在没有更新DATE字段的情况下“检查”？从例如PHP做到这一点？

Like, is there a way I can query the MySQL server and ask "Hey, is this DATE, TIME or DATETIME valid to you?"

比如，有没有办法可以查询MySQL服务器并询问“嘿，这个DATE，TIME或DATETIME对你有效吗？”

Or is there maybe an even better way of doing it?

或者是否有更好的方法呢？

12 个解决方案

#1

If you choose a server mode for the MySQL server that doesn't allow invalid date values a query containing such a malformed date representation will cause an error instead of (silently) assuming 0000-00-00
see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

如果为MySQL服务器选择不允许无效日期值的服务器模式，则包含这种格式错误的日期表示的查询将导致错误而不是（默默地）假设看到http：//dev.mysql。 COM / DOC / refman / 5.0 / EN /服务器-SQL mode.html

e.g.

例如

$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly'); 
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$pdo->exec('CREATE TEMPORARY TABLE foo (id int auto_increment, d datetime, primary key(id))');

$query = "INSERT INTO foo (d) VALUES ('2010-02-31 12:15:18')";
foreach( array('ALLOW_INVALID_DATES', 'STRICT_ALL_TABLES') as $mode ) {
  echo $mode, ": "; flush();
  $pdo->exec("SET SESSION sql_mode='$mode'");
  $pdo->exec($query);
  echo "Ok.\n";
}

prints

版画

ALLOW_INVALID_DATES: Ok.
STRICT_ALL_TABLES: 
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2010-02-31 12:15:18' for column 'd' at row 1' in [...]

#2

You could parse the date according to the format you want to use and then call checkdate to test if it's a valid date. Make sure you read the comments on http://php.net/manual/en/function.checkdate.php.

您可以根据要使用的格式解析日期，然后调用checkdate来测试它是否是有效日期。请务必阅读http://php.net/manual/en/function.checkdate.php上的评论。

#3

I use this syntax in my Application and it's work like charm!

我在我的应用程序中使用这种语法，它的工作就像魅力一样！

SELECT DATE('2013-09-31') AS valid;

My System is Win7x64 with PHP 5.5 and MySQL 5.6.16

我的系统是Win7x64，PHP 5.5和MySQL 5.6.16

#4

You could just use a 'constant' query, without using temporary tables and test fields:

您可以使用“常量”查询，而无需使用临时表和测试字段：

mysql> select day('2010-02-31 00:00:00');
+----------------------------+
| day('2010-02-31 00:00:00') |
+----------------------------+
|                       NULL | 
+----------------------------+

#5

Since the question states MySQL, here is a MySQL solution:

由于问题陈述MySQL，这是一个MySQL解决方案：

It is very difficult to verify if a field is a date because of all the different possible date formats that would need to be taken into account. BUT if you know that the field date formats are one of these:

由于需要考虑所有不同的可能日期格式，因此很难验证字段是否为日期。但是，如果您知道字段日期格式是以下之一：

'yyyy-mm-dd'
'yyyy-mm-dd hh:mm:ss'
'yyyy-mm-dd whatever'

This code will help you:

此代码将帮助您：

 SELECT count(*) FROM `table` 
 WHERE DATE(STR_TO_DATE(`column`, '%Y-%m-%d')) IS NOT NULL
 AND `column` NOT REGEXP '^[0-9\.]+$'

Basically :

基本上：

• the first condition tells you if is a date, but unfortunately doesn't exclude numbers (ex: DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00'
• 第一个条件告诉您是否是日期，但遗憾的是不排除数字（例如：DATE（STR_TO_DATE（** 1 **，'％Y-％m-％d'））='2001-00-00'
• the second ensures that numbers are excluded, which leaves you with dates only that follow the formats above.
• 第二个确保排除数字，只留下符合上述格式的日期。

If count(*) is >0 then it's a date, if it is 0 it's something else.

如果count（*）> 0那么它是一个日期，如果它是0则是其他的东西。

Note This method works for strings of any date format as long as you know in advance what format they follow (which I know is not always the case but still helpful). Just replace the format a priori in the query

注意此方法适用于任何日期格式的字符串，只要您事先知道它们遵循的格式（我知道情况并非总是如此，但仍然有用）。只需在查询中替换先验格式即可

#6

Use this function in php to check for a valid date:

在php中使用此函数来检查有效日期：

function valid_date($date) {
    return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date));
}

Or as @VolkerK said if an invalid date is inserted into the database it will be stored as 0000-00-00 so you could also do:

或者@VolkerK说如果在数据库中插入无效日期，它将被存储为0000-00-00，所以你也可以这样做：

SELECT * FROM table WHERE date_field > '0000-00-00'

SELECT * FROM table WHERE date_field>'0000-00-00'

#7

A simple PHP function that will validate MySQL data types of "date" and "datetime" is in code sample that follows (chk_MySQL_Datetime). It validates by using createFromFormat() to create a Date object for the date/date time if this fails then the date/datetime is invalid. If a Date Object is created, a formatted string created with date_format() and compared to the date/time . If the strings are equal the date/datetime is valid. If not equal the date/datetime are invalid. This comparison is necessary because createFromFormat() will accept 2009/02/29 09:85 as a valid datetime but MySQL will not.

验证MySQL数据类型“date”和“datetime”的简单PHP函数位于后面的代码示例中（chk_MySQL_Datetime）。它通过使用createFromFormat（）为日期/日期时间创建Date对象进行验证，如果失败则日期/日期时间无效。如果创建了日期对象，则使用date_format（）创建格式化字符串，并将其与日期/时间进行比较。如果字符串相等，则日期/日期时间有效。如果不相等，则日期/日期时间无效。这种比较是必要的，因为createFromFormat（）将接受2009/02/29 09:85作为有效的日期时间，但MySQL不会。

The following code includes an example/test case that shows usage and test against a MySQL database. To use the example you will have to replace $database->domysql_query() with call to a database. The output of the echos are included as comments immediately following the echo statements.

以下代码包含一个示例/测试用例，显示针对MySQL数据库的使用和测试。要使用该示例，您必须使用对数据库的调用替换$ database-> domysql_query（）。回声的输出作为注释包含在echo语句之后。

<?php
/**
 * Returns false if MySQL date or datetime value would be stored as
 * 0000-00-00  or 0000-00-00 00:00:00.
 * @param string $fmt - createFromFormat format of MySQL datetime value
 * to be tested. see http://www.php.net/manual/en/datetime.createfromformat.php
 * @param string $datetime MySQL datetime value
 * to be tested.
 * @return DateTime object formatted according to $fmt or FALSE if not
 * valid MySQL datetime value (would be inserted into the database
 * 0000-00-00 00:00:00);
 */
function chk_MySQL_Datetime($fmt, $datetime) {
    global $fmtdatetime;  // for example only
    $ckdate = DateTime::createFromFormat($fmt, $datetime);
    if ($ckdate !== FALSE) {
        $fmtdatetime = date_format($ckdate, $fmt);
        if ($fmtdatetime != $datetime) {
            $ckdate = FALSE;
        }
    }

    return $ckdate;
}
/*  Example/test of  chk_MySQL_Datetime */
/**
 * Creates table datetimetest if it doesn't exist
 * and insert a record in primary index of type datetime
 * then select record inserted and return result as a formated string.
 * @global type $database - addressibility to database functions
 * @global type $mysqlenum - MySql errornumber of last operation
 * @global type $fmtdatetime - $datetime formatted by date_format
 * @param type $datetime - datetime vale to be set
 * @return string of what was inserted
 */
function insert_in_table($datetime) {
    global $database, $mysqlenum, $fmtdatetime;
    $sql = "CREATE TABLE IF NOT EXISTS `datetimetest` (
  `MySQL_datetime` datetime NOT NULL COMMENT 'datetime created by MySQL',
  `in_datetime` varchar(90) NOT NULL COMMENT 'date time string',
  `fmtdatetime` varchar(90) NOT NULL COMMENT 'Output of createFromFormat',
  PRIMARY KEY (`MySQL_datetime`)
) ;";
    $result = $database->domysql_query($sql, $database->connection, true);

    $sql = "DELETE FROM `datetimetest`  WHERE MySQL_datetime='$datetime' OR `MySQL_datetime` = '0000-00-00 00:00:00'; ";
    $result = $database->domysql_query($sql, $database->connection, false);
    $sql = "INSERT INTO `datetimetest` (MySQL_datetime, in_datetime, fmtdatetime)
       VALUES ('$datetime', '$datetime', '$fmtdatetime')";
    $result = $database->domysql_query($sql, $database->connection, false);
    $sql = "SELECT * FROM `datetimetest`  WHERE MySQL_datetime='$datetime' OR `MySQL_datetime` = '0000-00-00 00:00:00'; ";
    $result = $database->domysql_query($sql, $database->connection, false);
    $contxa = mysql_fetch_assoc($result);

    $ret = " Inserted datetime = " . $contxa['in_datetime'] . "," .
            " MySQL stored " . $contxa['MySQL_datetime'] .
            ", fmtdatetime = " . $contxa['fmtdatetime'] . "<br>";
    return $ret;
}

        global $fmtdatetime;
        echo('<br>');
        $format = 'Y-m-d';
        $datetime = '2009-02-15'; // valid date
        $date = chk_MySQL_Datetime($format, $datetime);
        echo "Format: $format; datetime is " . ($date ? 'valid' : 'invalid' ) . " Expected $datetime is " . $fmtdatetime . "<br>";
 //echo output = Format: Y-m-d; datetime is valid Expected 2009-02-15 is 2009-02-15

        $result = insert_in_table($datetime);
        echo $result . "<br>";
 //echo output = Inserted datetime = 2009-02-15, MySQL stored 2009-02-15 00:00:00, fmtdatetime = 2009-02-15

        $datetime = '2009-02-29'; //invalid date
        $date = chk_MySQL_Datetime($format, $datetime);
        echo "Format: $format; datetime is " . ($date ? 'valid' : 'invalid' ) . " Expected $datetime is " . $fmtdatetime . "<br>";
 //echo output = Format: Y-m-d; datetime is invalid Expected 2009-02-29 is 2009-03-01

        $result = insert_in_table($datetime);
        echo $result . "<br>";
 //echo output =  Inserted datetime = 2009-02-29, MySQL stored 0000-00-00 00:00:00, fmtdatetime = 2009-03-01

        $format = 'Y-m-d H:i';
        $datetime = '2009-02-15 14:20';
        $date = chk_MySQL_Datetime($format, $datetime);
        echo "Format: $format; datetime is " . ($date ? 'valid' : 'invalid' ) . " Expected $datetime is " . $fmtdatetime . "<br>";
//echo output = Format: Y-m-d H:i; datetime is valid Expected 2009-02-15 14:20 is 2009-02-15 14:20

        $result = insert_in_table($datetime);
        echo $result . "<br>";
//echo output = Inserted datetime = 2009-02-15 14:20, MySQL stored 2009-02-15 14:20:00, fmtdatetime = 2009-02-15 14:20

        $datetime = '2009-02-15 14:63';  // invalid time
        $date = chk_MySQL_Datetime($format, $datetime);
        echo "Format: $format; datetime is " . ($date ? 'valid' : 'invalid' ) . " Expected $datetime is " . $fmtdatetime . "<br>";
//echo output = Format: Y-m-d H:i; datetime is invalid Expected 2009-02-15 14:63 is 2009-02-15 15:03

        $result = insert_in_table($datetime);
        echo $result . "<br>";
 //echo output = Inserted datetime = 2009-02-15 14:63, MySQL stored 0000-00-00 00:00:00, fmtdatetime = 2009-02-15 15:03

        $datetime = 'x-02-15 14:59';  // invalid time
        $date = chk_MySQL_Datetime($format, $datetime);
        echo "Format: $format; datetime is " . ($date ? 'valid' : 'invalid' ) . " Expected $datetime is " . $fmtdatetime . "<br>";
 //echo output = Format: Y-m-d H:i; datetime is invalid Expected x-02-15 14:59 is 2009-02-15 15:03

        $result = insert_in_table($datetime);
        echo $result . "<br>";
//echo output =  Inserted datetime = x-02-15 14:59, MySQL stored 0000-00-00 00:00:00, fmtdatetime = 2009-02-15 15:03
?>

#8

Maybe you have to create a database with BLACKHOLE engine, read here. And check the exception thrown by PDOException.

也许您必须使用BLACKHOLE引擎创建数据库，请阅读此处。并检查PDOException抛出的异常。

$dbh = new PDO('mysql:host=localhost;dbname=test', 'username', 'p@s5W0Rd!!!', array(PDO::ATTR_PERSISTENT => true));

// CREATE DATABASE test;
// CREATE TABLE test.foo ( bar DATETIME ) ENGINE=BLACKHOLE;

try {
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();

    $dateTime = new DateTime();
    $dateTime = $dateTime->format('c');
    $dbh->exec("INSERT INTO foo (bar) VALUES ('".$dateTime."')");
    $dbh->commit();
} catch (PDOException $e) {
    $errorInfo = $dbh->errorInfo();

    if ($e->getCode() === '22007'
        and $dbh->errorCode() === '22007'
        and $errorInfo[0] === '22007'
        and preg_match('/^incorrect datetime/', strtolower($errorInfo[2])) === 1) {

        throw new Exception($errorInfo[2], (int) $e->getCode());
    }
}

#9

DATETIME - in SQL

SELECT your_unreliable_datetime_column, CAST(your_unreliable_datetime_column as DATETIME)
FROM some_cool_table
WHERE
CAST(your_unreliable_datetime_column as DATETIME) IS NOT NULL
AND
your_unreliable_datetime_column = CAST(your_unreliable_datetime_column as DATETIME)

This should only select valid DATETIMEs, also should be applicable to DATE and TIME accordingly, but not tested...

这应该只选择有效的DATETIME，也应该相应适用于DATE和TIME，但是没有经过测试......

I did not found any documentation on CAST() return value on a convert failure, but documentation covers a special case - a possible failure - which returns NULL:

我没有找到关于转换失败的CAST（）返回值的任何文档，但文档涵盖了一个特殊情况 - 可能的失败 - 返回NULL：

For conversion of a “zero” date string to a date, CONVERT() and CAST() return NULL and produce a warning when the NO_ZERO_DATE SQL mode is enabled.

为了将“零”日期字符串转换为日期，CONVERT（）和CAST（）返回NULL并在启用NO_ZERO_DATE SQL模式时生成警告。

Also, from my tests it seems that CAST() behaves the same on any convert failure - returns NULL and generates warning.

此外，从我的测试中看来，CAST（）在任何转换失败时表现相同 - 返回NULL并生成警告。

I actually tested this on MariaDB 10.0.30

我实际上在MariaDB 10.0.30上进行了测试

#10

SELECT WEEK(col) IS NOT NULL AS valid;

SELECT WEEK（col）IS NOT NULL为有效;

OR one of the:

或其中一个：

SELECT DAYNAME(col) IS NOT NULL AS valid;
SELECT TO_DAYS(col) IS NOT NULL AS valid;
SELECT TO_SECONDS(col) IS NOT NULL AS valid;
SELECT WEEKDAY(col) IS NOT NULL AS valid;
SELECT WEEKOFYEAR(col) IS NOT NULL AS valid;
SELECT YEARWEEK(col) IS NOT NULL AS valid;

#11

I do this:

我这样做：

if(strtotime(trim($str)) {
    // persist trim($str) in db.        

}

#12

To check if a value is a date you can use day(dateToCheck) = 0

要检查值是否为日期，您可以使用day（dateToCheck）= 0