如何在 PostgreSQL 中以毫秒为单位存储时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37139033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store timestamp with milliseconds in PostgreSQL?
提问by XmikeX
I need to store timestamps in the format yyyy-mm-dd hh:mm:ss.SSS (SSS standing for milliseconds) in a postgreSQL database. Surely it would work as a text/or varchar, but I need to "work" with the timestamps (e.g. filter all instances prior a certain timestamp - considering milliseconds). How would I do that in PostgreSQL?
我需要在 postgreSQL 数据库中以 yyyy-mm-dd hh:mm:ss.SSS（SSS 代表毫秒）格式存储时间戳。当然它可以作为文本/或 varchar 工作，但我需要使用时间戳“工作”（例如过滤某个时间戳之前的所有实例 - 考虑毫秒）。我将如何在 PostgreSQL 中做到这一点？
Thanks a lot!
- mike
非常感谢！
- 迈克
回答by Alex Mantaut
I know this is an old question, but I had a similar problem.
我知道这是一个老问题，但我遇到了类似的问题。
You can use the timestamp(n) field, with n equals the number of digits of precision to store on the date (timestamp(3) would give you millisecond precision)
您可以使用 timestamp(n) 字段，其中 n 等于要存储在日期上的精度位数（timestamp(3) 将为您提供毫秒精度）
Example
例子
CREATE TABLE date_test (datetime timestamp(3) with time zone);
insert into date_test values(to_timestamp(1525745241.879));
select EXTRACT(epoch FROM datetime) from date_test;
Note: in current versions of postgres timestamp is stored as an integer, so it does not incur in floating point rounding issues
注意：在当前版本的 postgres 时间戳中存储为整数，因此不会出现浮点舍入问题
