mysql - 将 UTC 纪元转换为本地时间戳，用于 SQL 查询

Question

您好，我的 SQL 表中有以下信息:

从表中选择*

id          epic                    bid     offer   update_time
136449187   CS.D.EURGBP.TODAY.IP    7010.9  7011.9  1447854279749
136449186   CC.D.LCO.USS.IP         4430.1  4433.1  1447854279696
136449185   CS.D.USDJPY.TODAY.IP    12344.1 12344.9 1447854279679
136449184   CC.D.RB.USS.IP          12507   12527   1447854279636
136449179   IX.D.DAX.DAILY.IP       10955   10956   1447854279597
136449183   CS.D.COPPER.TODAY.IP    4610.8  4620.8  1447854279593
136449182   CC.D.HO.USS.IP          13969   13989   1447854279561
136449181   CS.D.EURCHF.TODAY.IP    10837.7 10839.7 1447854279559

其中 update_time 是 UTC 纪元(整数)

如何才能仅提取 2015-11-18 08:00:00(欧洲)和 2015-11-18 21:30:00(欧洲)之间的行

我不确定如何将本地时间戳转换为 MYSQL 中的 UTC 等效值，以便我可以提取相关行。

Answer 1

试试这个:

select from_unixtime(substr(update_time,1,length(update_time)-3)) from table;

最后 3 位数字是毫秒，您无法显示

+------------+------------------------+---------------------+
| up_time    | from_unixtime(up_time) | date                |
+------------+------------------------+---------------------+
| 1435870857 | 2015-07-02 17:00:57    | 2015-07-02 17:00:57 |
|  899413267 | 1998-07-02 17:01:07    | 1998-07-02 17:01:07 |
| 1435870867 | 2015-07-02 17:01:07    | 2015-07-02 17:01:07 |
| 1436760000 | 2015-07-13 00:00:00    | 2015-07-13 00:00:00 |
| 1436760000 | 2015-07-13 00:00:00    | 2015-07-13 00:00:00 |
| 1436984796 | 2015-07-15 14:26:36    | 2015-07-15 14:26:36 |
| 1436984977 | 2015-07-15 14:29:37    | 2015-07-15 14:29:37 |
| 1436984993 | 2015-07-15 14:29:53    | 2015-07-15 14:29:53 |
| 1436587200 | 2015-07-11 00:00:00    | 2015-07-11 00:00:00 |
| 1437451200 | 2015-07-21 00:00:00    | 2015-07-21 00:00:00 |
| 1437443325 | 2015-07-20 21:48:45    | 2015-07-20 21:48:45 |
| 1437471045 | 2015-07-21 05:30:45    | 2015-07-21 05:30:45 |
| 1437442185 | 2015-07-20 21:29:45    | 2015-07-20 21:29:45 |
| 1437470985 | 2015-07-21 05:29:45    | 2015-07-21 05:29:45 |
| 1437463845 | 2015-07-21 03:30:45    | 2015-07-21 03:30:45 |
| 1447696656 | 2015-11-16 12:57:36    | 2015-11-16 12:57:36 |
| 1447696628 | 2015-11-16 12:57:08    | 2015-11-16 12:57:08 |
+------------+------------------------+---------------------+