MySQL: Comment vérifier si une chaîne de caractères est une DATE, une heure ou une DATETIME valide
quand J'essaie de mettre une valeur dans un champ DATE qui n'est pas valide, MySQL semble utiliser 0000-00-00 à la place. Y a-t-il un moyen de faire cette "vérification" sans mettre à jour un champ DATE? Et le faire à partir par exemple de PHP?
comme, est-ce qu'il y a un moyen pour que je puisse interroger le serveur MySQL et demander "Hé, est-ce que cette DATE, heure ou DATETIME est valide pour vous?"
Ou est-il peut-être encore la meilleure façon de le faire?
12 réponses
si vous choisissez un mode serveur pour le serveur MySQL qui ne permet pas les valeurs de date invalides une requête contenant une telle représentation de date mal formée causera une erreur au lieu de (silencieusement) supposer 0000-00-00
voir http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
par exemple
$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";
}
imprime
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 [...]
Vous pu analyser la date selon le format que vous souhaitez utiliser, puis appeler checkdate pour tester si c'est une date valide. Assurez-vous de lire les commentaires sur http://php.net/manual/en/function.checkdate.php.
j'utilise cette syntaxe dans mon Application et ça marche comme du charme!
SELECT DATE('2013-09-31') AS valid;
mon système est Win7x64 avec PHP 5.5 et MySQL 5.6.16
Vous pouvez simplement utiliser une requête 'constante', sans utiliser les tables temporaires et les champs de test:
mysql> select day('2010-02-31 00:00:00');
+----------------------------+
| day('2010-02-31 00:00:00') |
+----------------------------+
| NULL |
+----------------------------+
puisque la question dit MySQL voici un MySQL solution:
il est très difficile de vérifier si un champ est une date en raison des différents formats possibles de date qui devraient être pris en compte. Mais si vous savez que les formats de date de champ sont l'un de ceux-ci:
'yyyy-mm-dd'
'yyyy-mm-dd hh:mm:ss'
'yyyy-mm-dd whatever'
Ce code va vous aider:
SELECT count(*) FROM `table`
WHERE DATE(STR_TO_DATE(`column`, '%Y-%m-%d')) IS NOT NULL
AND `column` NOT REGEXP '^[0-9\.]+$'
en gros :
- la première condition vous dit si c'est une date, mais malheureusement n'exclut pas les nombres (ex:
DATE(STR_TO_DATE(**1**, '%Y-%m-%d')) = '2001-00-00' - le second assure que les nombres sont exclus, ce qui vous laisse avec des dates seulement qui suivent les formats ci-dessus.
Si count(*)>0 ensuite, c'est une date, si elle est 0 c'est autre chose.
Remarque:
Cette méthode fonctionne pour les chaînes de format de date aussi longtemps que vous savez à l'avance ce format qu'ils suivent (qui je sais n'est pas toujours le cas, mais toujours utile). Juste remplacer le format a priori dans la requête
SELECT WEEK(col) IS NOT NULL AS valid;
OU l'une de l':
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;
utilisez cette fonction en php pour vérifier une date valide:
function valid_date($date) {
return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date));
}
ou comme @VolkerK a dit si une date invalide est insérée dans la base de données il sera stocké comme 0000-00-00 de sorte que vous pouvez également faire:
SELECT * FROM table WHERE date_field > '0000-00-00'
une simple fonction PHP qui va valider les types de données MySQL de "date" et " datetime" est dans l'échantillon de code qui suit (chk_MySQL_Datetime). Il valide en utilisant createFromFormat () pour créer un objet Date pour la date/heure date si cela échoue, alors la date/datetime n'est pas valide. Si un objet Date est créé, une chaîne formatée créée avec date_format () et comparée à la date/heure . Si les chaînes sont égales, la date / datetime est valide. Si pas égale à la date/datetime ne sont pas valides. Ce comparaison est nécessaire car createFromFormat () acceptera 2009/02/29 09: 85 comme datetime valide mais MySQL ne le fera pas.
le code suivant inclut un exemple / cas de test qui montre l'utilisation et le test par rapport à une base de données MySQL. Pour utiliser cet exemple, vous devrez remplacer $database - > domysql_query() par call to a database. Les résultats des echos sont inclus en tant que Commentaires immédiatement après les déclarations d'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
?>
Peut-être que vous devez créer une base de données avec BLACKHOLE le moteur, lire ici. Et vérifiez l'exception lancée par 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());
}
}
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)
ceci ne doit sélectionner que valide DATETIMEs, aussi devrait être applicable à DATE et TIME en conséquence, mais pas testé...
je n'ai pas trouvé de documentation sur CAST() valeur de retour sur un convertir à l'échec, mais la documentation couvre un cas particulier - un éventuel échec - qui renvoie NULL:
pour la conversion d'une chaîne de date" zéro " en a la date, l'
CONVERT()etCAST()retourNULLet de produire un avertissement lorsque leNO_ZERO_DATEle mode SQL est permettre.
Aussi, de mes tests il sembleCAST() se comporte de la même manière sur convertir en échec - retourne NULL et génère un avertissement.
j'ai effectivement testé sur MariaDB 10.0.30
je fais ceci:
if(strtotime(trim($str)) {
// persist trim($str) in db.
}
À vérifier si une valeur est une date, vous pouvez utiliser
day(dateToCheck) = 0