PlaceOweb Dotclear

La sélection d'une date avec NOW() ou CURRENT_TIMESTAMP ne fonctionne plus sur des colonnes DATE depuis les nouvelles versions MySQL :

• 5.0.42
• 5.1.18

--
-- Structure de la table `test_date`
--
 
CREATE TABLE IF NOT EXISTS `test_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ladate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
 
--
-- Contenu de la table `test_date`
--
 
INSERT INTO `test_date` (`id`, `ladate`) VALUES
(1, '2009-10-27');

Sur MySQL 5.1

SELECT * 
FROM  `test_date` 
WHERE ladate = NOW();
-- MySQL n'a retourné aucun enregistrement. 
 
SELECT * 
FROM  `test_date` 
WHERE ladate = CAST( NOW() AS DATE )
-- Affichage des enregistrements 0 - 0 (1 total
 
SELECT * 
FROM  `test_date` 
WHERE ladate = CAST( CURRENT_TIMESTAMP AS DATE )
-- Affichage des enregistrements 0 - 0 (1 total
 
SELECT * 
FROM  `test_date` 
WHERE ladate = DATE_FORMAT(CURRENT_TIMESTAMP,GET_FORMAT(DATE,'ISO'))
-- Affichage des enregistrements 0 - 0 (1 total
 
SELECT * 
FROM  `test_date` 
WHERE ladate = DATE_FORMAT(NOW(),'%Y-%m-%d')
-- Affichage des enregistrements 0 - 0 (1 total

Voyez le beug MySQL Comparing date field with NOW() broken

Currently when a DATE value is compared to a DATETIME value the former is coerced to the
DATETIME type by adding zero time part (i.e. "DATE 00:00:00") and then values are
compared. DATE values compared as DATE and DATETIME values as DATETIME.
To get the old behaviour one should use the CAST() function:
SELECT date_field = CAST(NOW() as DATE);

Prior to this release, when DATE values were compared with DATETIME
values, the time portion of the DATETIME value was ignored, or the
comparison could be performed as a string compare. Now a DATE value 
is coerced to the DATETIME type by adding the time portion as
00:00:00. To mimic the old behavior, use the CAST() function to cause
the comparison operands to be treated as previously. For example:
SELECT date_col = CAST(NOW() AS DATE);

Et la documentation :

• MySQL 5.1 Reference Manual : Date and Time Types
• MySQL 5.0 Reference Manual : Date and Time Types