MySQL will store datetime / timestamp types internally in binary format. The format that is displayed to you does not necessarily reflect the internal format.
To work with datetime / timestamp types in seconds use the TO_SECONDS() function.
A query that compares a datetime / timestamp with an integer value will work because MySQL does type conversion automatically, as explained in the documentation link .
See this example:
mysql> USE test1;
Database changed
mysql> CREATE TABLE table1 (
-> added_id INT NOT NULL auto_increment,
-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (added_id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO table1 VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
mysql> INSERT INTO table1 VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT added_id, updated_at, TO_SECONDS(updated_at), updated_at+0 FROM table1;
+----------+---------------------+------------------------+----------------+
| added_id | updated_at | TO_SECONDS(updated_at) | updated_at+0 |
+----------+---------------------+------------------------+----------------+
| 1 | 2017-06-08 20:50:17 | 63664174217 | 20170608205017 |
| 2 | 2017-06-08 20:50:22 | 63664174222 | 20170608205022 |
+----------+---------------------+------------------------+----------------+
2 rows in set (0.00 sec)
Note that the TO_SECONDS() function returns the value in seconds since year 0.
Note also that when doing a zero-sum operation, the command is accepted, but we see that the stored integer value does not match the seconds.
The two queries below will work:
mysql> SELECT added_id, updated_at FROM table1 WHERE updated_at > 20170608205017
;
+----------+---------------------+
| added_id | updated_at |
+----------+---------------------+
| 2 | 2017-06-08 20:50:22 |
+----------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM table1 WHERE TO_SECONDS(updated_at) > 63664174217;
+----------+---------------------+
| added_id | updated_at |
+----------+---------------------+
| 2 | 2017-06-08 20:50:22 |
+----------+---------------------+
1 row in set (0.00 sec)
