Mysql auto timestamp insertion

Asked

Viewed 794 times

1

I am using the option ON UPDATE CURRRENT_TIMESTAMP, but the format that is updated in the table comes in this format: "2017-06-07 13:05:02", I wish it were in seconds like this: 1496851485, the most curious is that when I make the comparison of these types of timestamp in a statment the condition is given as true, for example:

"SELECT author, comment, timestamp FROM comments WHERE timestamp > :requestedTimestamp"

being the timestamp of the bank in the format "2017-06-07 13:05:02" and :requestedTimestamp 1496851485, Does Mysql auto-convert or is this a bug? because even if I put a very old date it still works...

mysql

1 answer

0

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 function TO_SECONDS().

A query that compares a datetime/timestamp with an entire value will work because Mysql automatically converts types, as the documentation explains https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-conversion.html.

Take 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 function TO_SECONDS() brings the value in seconds since year 0. Also note that when doing a date sum operation with zero, the command is accepted, but we check that the stored integer does not correspond to 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)

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.