MySQL Bugs: #58848: FROM_UNIXTIME will not compare to STR_TO

Bug #58848	FROM_UNIXTIME will not compare to STR_TO_DATE
Submitted:	9 Dec 2010 21:06	Modified:	14 Jan 2011 21:05
Reporter:	Craig Monson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5.5-m3-log	OS:	Linux (2.6.24-10-xen)
Assigned to:		CPU Architecture:	Any
Tags:	from_unixtime, STR_TO_DATE, unix_timestamp

Description:
Just a little more basic info:

mysql> show variables like '%version%'
;+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.1                        | 
| protocol_version        | 10                           | 
| slave_type_conversions  |                              | 
| version                 | 5.5.5-m3-log                 | 
| version_comment         | MySQL Community Server (GPL) | 
| version_compile_machine | x86_64                       | 
| version_compile_os      | linux2.6                     | 
+-------------------------+------------------------------+

It did work in an older version:
mysql> show variables like '%version%';
+-------------------------+--------------------+
| Variable_name           | Value              |
+-------------------------+--------------------+
| protocol_version        | 10                 | 
| version                 | 5.0.51a-3ubuntu5.7 | 
| version_comment         | (Ubuntu)           | 
| version_compile_machine | i486               | 
| version_compile_os      | debian-linux-gnu   | 
+-------------------------+--------------------+

I found that int columns that are actually unix timestamps won't match when converting to a date, however, when converting the date to a unix timestamp it will... think the example will show better.

How to repeat:
create table temp_test (u_date int not null);

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2010-12-09 20:58:58 | 
+---------------------+

mysql> select UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1291928324 | 
+-----------------------+

mysql>insert into temp_test values (1291928324);

mysql> select * from temp_test
where FROM_UNIXTIME(u_date)
BETWEEN STR_TO_DATE(201001010000, '%Y%m%d%H%i%s')
AND STR_TO_DATE(20101225000000, '%Y%m%d%H%i%s');
Empty set (0.00 sec)

mysql> select * from temp_test
where u_date
BETWEEN UNIX_TIMESTAMP(STR_TO_DATE(201001010000, '%Y%m%d%H%i%s'))
AND UNIX_TIMESTAMP(STR_TO_DATE(20101225000000, '%Y%m%d%H%i%s'));
+------------+
| u_date     |
+------------+
| 1291928324 | 
+------------+

Suggested fix:
wish I had one!

Thank you for the report.

I can not repeat described behavior. Please try with current version 5.5.7 and if problem still exists indicate exact name of package you are using (file name you downloaded) and provide your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".