时间戳格式-从1/1000秒到1/100秒 [英] timestamp formatting - from 1/1000th of a sec to 1/100th of sec

问题描述

需要将1/1000秒分辨率的时间戳转换为1/100分辨率。我可以为此目的使用 to_char（timestamp，text）格式化功能，但是需要帮助使用 text

Need to convert timestamps with 1/1000 second resolution to 1/100 resolution. I could possibly use to_char(timestamp, text) formatting function for this purpose, however need help with text to be used here.

输入表（注意-此处的时间戳存储为varchar）

input table (note - the timestamps here are stored as varchar)

+-------------------------+
|       ms1000_val        |
+-------------------------+
| 2017/02/20 08:27:17.899 |
| 2017/02/20 08:23:43.894 |
| 2017/02/20 08:24:41.894 |
| 2017/02/20 08:28:09.899 |
+-------------------------+

输出表

+------------------------+
|       ms100_val        |
+------------------------+
| 2017/02/20 08:27:17.89 |
| 2017/02/20 08:23:43.89 |
| 2017/02/20 08:24:41.89 |
| 2017/02/20 08:28:09.89 |
+------------------------+

推荐答案

您可以在方括号中指定它，例如：

You can specify it in brackets, like here:

t=# select now()::timestamp(2);
          now
------------------------
 2017-03-16 09:55:21.15
(1 row)

OP注意到 http://rextester.com/CBZ17212 产生不同的结果，然后在 psql ：

as OP noticed http://rextester.com/CBZ17212 produces different result then running in psql:

t=# CREATE TABLE Table1
t-#     ("ms1000_val" varchar(23))
t-# ;
CREATE TABLE
t=#
t=# INSERT INTO Table1
t-#     ("ms1000_val")
t-# VALUES
t-#     ('2017/02/20 08:27:17.892'),
t-#     ('2017/02/20 08:23:43.891'),
t-#     ('2017/02/20 08:24:41.897'),
t-#     ('2017/02/20 08:28:09.893')
t-# ;
INSERT 0 4
t=# select ms1000_val::timestamp(2) as time_formatted
t-#        from Table1;
     time_formatted
------------------------
 2017-02-20 08:27:17.89
 2017-02-20 08:23:43.89
 2017-02-20 08:24:41.9
 2017-02-20 08:28:09.89
(4 rows)

这篇关于时间戳格式-从1/1000秒到1/100秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！