虽然我无法重现这个问题,但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)