代码之家 › 专栏 › 技术社区 › user7987898

MySQL shell中奇怪的时间戳输出

timestamp mysql

user7987898 · 技术社区 · 7 年前

mysql-sql> select time_stamp, unix_timestamp(time_stamp) from historical_data 
where time_stamp > '2014-07-31' and time_stamp < '2014-10-05';
+---------------------+----------------------------+
| time_stamp          | unix_timestamp(time_stamp) |
+---------------------+----------------------------+
| 2014-07-31 19:04:00 |                 1406826240 |
| 2014-00-00 0:00:00  |                 1406913840 |
| 2014-00-00 0:00:00  |                 1407002040 |
| 2014-00-00 0:00:00  |                 1407088440 |
| 2014-00-00 0:00:00  |                 1407174840 |

这发生在2014年8月1日至2014年10月1日,然后恢复正常产出。

不确定以下内容是否相关,但我不知道是什么原因导致了这种情况的发生:

以下是MySQL shell中从头开始出现的问题:

mysql-sql> select version();
+------------+
| version()  |
+------------+
| 5.7.18-log |
+------------+
1 row in set (0.00 sec)
mysql-sql> create table test(`time_stamp` TIMESTAMP NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)
mysql-sql> insert into test (time_stamp) values ('2014-07-31');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-08-15');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-09-15');
Query OK, 1 row affected (0.01 sec)
mysql-sql> insert into test (time_stamp) values ('2014-10-01');
Query OK, 1 row affected (0.00 sec)
mysql-sql> select * from test;
+--------------------+
| time_stamp         |
+--------------------+
| 2014-07-31 0:00:00 |
| 2014-00-00 0:00:00 |
| 2014-00-00 0:00:00 |
| 2014-10-01 0:00:00 |
+--------------------+
4 rows in set (0.00 sec)
mysql-sql> select time_stamp, unix_timestamp(time_stamp) from test;
+--------------------+----------------------------+
| time_stamp         | unix_timestamp(time_stamp) |
+--------------------+----------------------------+
| 2014-07-31 0:00:00 |                 1406757600 |
| 2014-00-00 0:00:00 |                 1408053600 |
| 2014-00-00 0:00:00 |                 1410732000 |
| 2014-10-01 0:00:00 |                 1412114400 |
+--------------------+----------------------------+
4 rows in set (0.00 sec)

所以至少我知道问题不在应用层。

1 回复 | 直到 7 年前

wchiquito 7 年前

虽然我无法重现这个问题,但MySQL Shell中显然存在一个bug(请参阅 INSERT SELECT ):

Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in SQL mode. Use \js or \py to switch the shell to a scripting language.
mysql-sql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql-sql> DROP TABLE IF EXISTS `_`.`historical_data`;
Query OK, 0 rows affected (0.03 sec)

mysql-sql> CREATE TABLE IF NOT EXISTS `_`.`historical_data` (
       ...   `time_stamp` TIMESTAMP NOT NULL PRIMARY KEY
       ... );
Query OK, 0 rows affected (0.00 sec)

mysql-sql> INSERT INTO `_`.`historical_data`
       ...   (`time_stamp`)
       ... VALUES
       ...   ('2014-07-31 19:04:00'),
       ...   ('2014-07-31 17:04:00'),
       ...   ('2014-08-01 17:24:00'),
       ...   ('2014-08-02 17:54:00'),
       ...   ('2014-08-03 17:54:00'),
       ...   ('2014-08-04 17:54:00'); 
Query OK, 6 rows affected (0.00 sec)

mysql-sql> SELECT
       ...   `time_stamp`
       ... FROM
       ...   `_`.`historical_data`;
+---------------------+
| time_stamp          |
+---------------------+
| 2014-08-31 17:04:00 |
| 2014-08-31 19:04:00 |
| 2014-09-01 17:24:00 |
| 2014-09-02 17:54:00 |
| 2014-09-03 17:54:00 |
| 2014-09-04 17:54:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql-sql> SELECT
       ...   `der`.`time_stamp`,
       ...   `der`.`ut`,
       ...   FROM_UNIXTIME(`der`.`ut`)
       ... FROM (
       ...   SELECT
       ...     `time_stamp`,
       ...     UNIX_TIMESTAMP(`time_stamp`) `ut`
       ...   FROM
       ...     `_`.`historical_data`
       ...   WHERE
       ...     `time_stamp` > '2014-07-31' AND
       ...     `time_stamp` < '2014-10-05'
       ... ) `der`;
+---------------------+------------+---------------------------+
| time_stamp          | ut         | FROM_UNIXTIME(`der`.`ut`) |
+---------------------+------------+---------------------------+
| 2014-08-31 17:04:00 | 1406826240 | 2014-08-31 17:04:00       |
| 2014-08-31 19:04:00 | 1406833440 | 2014-08-31 19:04:00       |
| 2014-09-01 17:24:00 | 1406913840 | 2014-09-01 17:24:00       |
| 2014-09-02 17:54:00 | 1407002040 | 2014-09-02 17:54:00       |
| 2014-09-03 17:54:00 | 1407088440 | 2014-09-03 17:54:00       |
| 2014-09-04 17:54:00 | 1407174840 | 2014-09-04 17:54:00       |
+---------------------+------------+---------------------------+
6 rows in set (0.00 sec)

mysql-sql> SELECT FROM_UNIXTIME('1406826240'),
       ...        FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S');
+-----------------------------+--------------------------------------------------+
| FROM_UNIXTIME('1406826240') | FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S') |
+-----------------------------+--------------------------------------------------+
| 2014-08-31 17:04:00         | 2014-07-31 17:04:00                              |
+-----------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

MySQL命令行 :

mysql> SELECT FROM_UNIXTIME('1406826240'),
    ->        FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S');
+-----------------------------+--------------------------------------------------+
| FROM_UNIXTIME('1406826240') | FROM_UNIXTIME('1406826240', '%Y-%m-%d %H:%i:%S') |
+-----------------------------+--------------------------------------------------+
| 2014-07-31 17:04:00.000000  | 2014-07-31 17:04:00                              |
+-----------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

MySQL外壳 :

mysql-sql> SELECT
       ...   CONCAT(`time_stamp`) `time_stamp`
       ... FROM
       ...   `_`.`historical_data`;
+---------------------+
| time_stamp          |
+---------------------+
| 2014-07-31 17:04:00 |
| 2014-07-31 19:04:00 |
| 2014-08-01 17:24:00 |
| 2014-08-02 17:54:00 |
| 2014-08-03 17:54:00 |
| 2014-08-04 17:54:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql-sql> SELECT
       ...   `der`.`time_stamp`,
       ...   `der`.`ut`,
       ...   CONCAT(FROM_UNIXTIME(`der`.`ut`))
       ... FROM (
       ...   SELECT
       ...     CONCAT(`time_stamp`) `time_stamp`,
       ...     UNIX_TIMESTAMP(`time_stamp`) `ut`
       ...   FROM
       ...     `_`.`historical_data`
       ...   WHERE
       ...     `time_stamp` > '2014-07-31' AND
       ...     `time_stamp` < '2014-10-05'
       ... ) `der`;
+---------------------+------------+-----------------------------------+
| time_stamp          | ut         | CONCAT(FROM_UNIXTIME(`der`.`ut`)) |
+---------------------+------------+-----------------------------------+
| 2014-07-31 17:04:00 | 1406826240 | 2014-07-31 17:04:00               |
| 2014-07-31 19:04:00 | 1406833440 | 2014-07-31 19:04:00               |
| 2014-08-01 17:24:00 | 1406913840 | 2014-08-01 17:24:00               |
| 2014-08-02 17:54:00 | 1407002040 | 2014-08-02 17:54:00               |
| 2014-08-03 17:54:00 | 1407088440 | 2014-08-03 17:54:00               |
| 2014-08-04 17:54:00 | 1407174840 | 2014-08-04 17:54:00               |
+---------------------+------------+-----------------------------------+
6 rows in set (0.01 sec)