| Bug #41787 | date comparsion issues over the new year | ||
|---|---|---|---|
| Submitted: | 29 Dec 2008 18:41 | Modified: | 30 Dec 2008 16:28 |
| Reporter: | Eric Ireland | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.0.60, 5.0.70, 5.0.51 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date, new year. | ||
[30 Dec 2008 0:43]
MySQL Verification Team
Thank you for the bug report. Could you please provide the create table command and the expected results and the actual results you are getting. Thanks in advance.
[30 Dec 2008 13:57]
Eric Ireland
Thanks for your reponse. Here is the create table statment:
CREATE TABLE `orders_sent` (
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`order_number` VARCHAR(10) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`order_type` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`po_number` VARCHAR(25) COLLATE latin1_general_ci DEFAULT NULL,
`freight_pay` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`freight_pay_specified` VARCHAR(50) COLLATE latin1_general_ci DEFAULT NULL,
`msds` VARCHAR(1) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`billing` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`billing_specified` VARCHAR(50) COLLATE latin1_general_ci DEFAULT NULL,
`ship_via` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`ship_via_specified` VARCHAR(50) COLLATE latin1_general_ci DEFAULT NULL,
`ship_to_number` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`bill_to_number` VARCHAR(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`tax_exempt_number` VARCHAR(50) COLLATE latin1_general_ci DEFAULT NULL,
`special_instructions` TEXT COLLATE latin1_general_ci,
`card_type` VARCHAR(25) COLLATE latin1_general_ci DEFAULT NULL,
`name_on_card` VARCHAR(50) COLLATE latin1_general_ci DEFAULT NULL,
`card_number` VARCHAR(25) COLLATE latin1_general_ci DEFAULT NULL,
`exp_month` VARCHAR(2) COLLATE latin1_general_ci DEFAULT NULL,
`exp_year` VARCHAR(4) COLLATE latin1_general_ci DEFAULT NULL,
`order_date` DATE NOT NULL,
`sent_date` VARCHAR(20) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `order_number` (`order_number`)
)ENGINE=MyISAM
AUTO_INCREMENT=5 CHARACTER SET 'latin1' COLLATE 'latin1_general_ci'
COMMENT='Holds the order pad info.';
COMMIT;
The actual results:
mysql> select order_number,order_type,order_date,ship_to_number from
-> orders_sent where str_to_date(sent_date,'%b %d %X %T') >=
-> STR_TO_DATE('Dec 26 2008 18:01:00','%b %d %X %T') and
-> str_to_date(sent_date,'%b %d %X %T') <= STR_TO_DATE('Jan 02 2009
'> 18:00:00','%b %d %X %T');
Empty set (0.00 sec)
expected results:
mysql> select order_number,order_type,order_date,ship_to_number from
-> orders_sent where str_to_date(sent_date,'%b %d %X %T') >=
-> STR_TO_DATE('Dec 26 2008 18:01:00','%b %d %X %T') and
-> str_to_date(sent_date,'%b %d %X %T') <= STR_TO_DATE('Dec 31 2008
'> 18:00:00','%b %d %X %T');
+--------------+------------+------------+----------------+
| order_number | order_type | order_date | ship_to_number |
+--------------+------------+------------+----------------+
| 10 | Share | 2008-12-11 | 113119 |
| 11 | Share | 2008-12-29 | 15586 |
| 61 | Titan | 2008-12-29 | 104337 |
+--------------+------------+------------+----------------+
3 rows in set (0.00 sec)
Thanks again!
[30 Dec 2008 16:28]
Sveta Smirnova
Thank you for the report.
You have typo in the query:
Original query: select order_number,order_type,order_date,ship_to_number from
-> orders_sent where str_to_date(sent_date,'%b %d %X %T') >=
-> STR_TO_DATE('Dec 26 2008 18:01:00','%b %d %X %T') and
-> str_to_date(sent_date,'%b %d %X %T') <= STR_TO_DATE('Jan 02 2009
'> 18:00:00','%b %d %X %T');
You have %X modifier:
$mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5009
Server version: 5.1.30-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select STR_TO_DATE('Jan 02 2009 18:00:00','%b %d %X %T');
+---------------------------------------------------+
| STR_TO_DATE('Jan 02 2009 18:00:00','%b %d %X %T') |
+---------------------------------------------------+
| 0000-01-02 18:00:00 |
+---------------------------------------------------+
1 row in set (0.18 sec)
mysql> select STR_TO_DATE('Nov 10 2008 10:55:39','%b %d %X %T');
+---------------------------------------------------+
| STR_TO_DATE('Nov 10 2008 10:55:39','%b %d %X %T') |
+---------------------------------------------------+
| 0000-11-10 10:55:39 |
+---------------------------------------------------+
1 row in set (0.00 sec)
So this is expected '0000-11-....' > '0000-01-....'
With modifier %Y date is correct:
mysql> select STR_TO_DATE('Nov 10 2008 10:55:39','%b %d %Y %T');
+---------------------------------------------------+
| STR_TO_DATE('Nov 10 2008 10:55:39','%b %d %Y %T') |
+---------------------------------------------------+
| 2008-11-10 10:55:39 |
+---------------------------------------------------+
1 row in set (0.00 sec)
So I close the report as "Not a Bug".
Description: Doing a date comparison over the new year returns a null set when data is expected(doing a select over a date range that includes the new year). Found/tested on 5.0.51 on windows, tested against 5.0.60 and 5.0.72 on linux. How to repeat: Import the following table/data INSERT INTO `orders_sent` (`id`, `order_number`, `order_type`, `po_number`, `freight_pay`, `freight_pay_specified`, `msds`, `billing`, `billing_specified`, `ship_via`, `ship_via_specified`, `ship_to_number`, `bill_to_number`, `tax_exempt_number`, `special_instructions`, `card_type`, `name_on_card`, `card_number`, `exp_month`, `exp_year`, `order_date`, `sent_date`) VALUES (1, '70', 'WF', 'none', 'FOB origin', 'none', 'N', 'Net 30', 'none', 'Best Way', 'none', '113119', '114157', '', '', '', '', '', '', '', '2008-11-10', 'Nov 10 2008 10:55:39'), (2, '10', 'S', 'none', 'FOB origin', 'none', 'N', 'Net 30', 'none', 'Best Way', 'none', '113119', '114157', '', '', '', '', '', '', '', '2008-12-11', 'Dec 29 2008 11:33:40'), (3, '11', 'S', 'none', 'FOB origin', 'none', 'N', 'Net 30', 'none', 'Best Way', 'none', '15586', '15586', '', '', '', '', '', '', '', '2008-12-29', 'Dec 29 2008 11:33:40'), (4, '61', 'T', 'none', 'FOB origin', 'none', 'N', 'Net 30', 'none', 'Best Way', 'none', '104337', '104336', '', '', '', '', '', '', '', '2008-12-29', 'Dec 29 2008 11:33:40'); run the following query: select order_number,order_type,order_date,ship_to_number from orders_sent where str_to_date(sent_date,'%b %d %X %T') >= STR_TO_DATE('Dec 26 2008 18:01:00','%b %d %X %T') and str_to_date(sent_date,'%b %d %X %T') <= STR_TO_DATE('Jan 02 2009 18:00:00','%b %d %X %T'); Expected results: Run this query: select order_number,order_type,order_date,ship_to_number from orders_sent where str_to_date(sent_date,'%b %d %X %T') >= STR_TO_DATE('Dec 26 2008 18:01:00','%b %d %X %T') and str_to_date(sent_date,'%b %d %X %T') <= STR_TO_DATE('Dec 31 2008 18:00:00','%b %d %X %T');