Not the answer you need?
Register and ask your own question!

Timestamp column query output issue

vaibhav_upadhyay40vaibhav_upadhyay40 ContributorCurrent User Role Patron
I have a table having timestamp column (c_created_time).

While firing  a select query using  c_created_time in select and also same column in where clause it shows inaccurate result.
And
When i use min function with same where clause as above query it shows different result which is accurate (as expected).
2nd query output is correct.
 
Note: Minimum value in this table for timestamp column is "2020-06-13 00:00:00" (expected value)

=================
Table info
==================
show create table abc \G
*************************** 1. row ***************************
    Table: abc
Create Table: CREATE TABLE `abc` (
  `c_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `e_id` bigint(20) NOT NULL,
  `p_id` varchar(30) NOT NULL,
  `c_created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`c_id`,`c_created_time`),
  KEY `p_id_idx` (`p_id`)
) ENGINE=InnoDB;

Query 1 : 
mysql> select c_created_time from abc where c_created_time>'1990-01-01 00:00:00' limit 1;
+---------------------+
| c_created_time      |
+---------------------+
| 2020-06-17 12:52:46 |
+---------------------+
mysql>

Query 2 : 
mysql>  select min(c_created_time) from abc where c_created_time > '1990-01-01 00:00:00';
+---------------------+
| min(c_created_time) |
+---------------------+
| 2020-06-13 00:00:00 |
+---------------------+

Thank you.

Comments

  • KryKry Current User Role Contributor
    edited October 29

    Hey.

    Your query works correctly because '2020-06-17 12:52:46'> '1990-01-01 00:00:00'.

    Perhaps for a query without the "MIN" function, you missed "ORDER BY c_created_time" for the correct result?

    SELECT

    c_created_time

    FROM abc

    WHERE c_created_time > '1990-01-01 00:00:00' 

    ORDER BY c_created_time

    LIMIT 1;

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.