SQL 从长时间的postgres转换日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7872720/
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
Convert date from long time postgres
提问by Nirmal- thInk beYond
I want to select date(readable string) from epoch time from one column that have time in longmilliseconds in postgres like
我想从一列的纪元时间中选择日期（可读字符串），这些列long在 postgres 中以毫秒为单位
select *, to_date(time in milli sec) from mytable
select *, to_date(time in milli sec) from mytable
how to do?
怎么做？
回答by Yahia
use
用
select *, to_timestamp(time in milli sec / 1000) from mytable
For reference see the postgres docs at http://www.postgresql.org/docs/9.0/static/functions-formatting.html
如需参考，请参阅http://www.postgresql.org/docs/9.0/static/functions-formatting.html 上的 postgres 文档
回答by Erwin Brandstetter
SELECT timestamp 'epoch' + time_in_millisec * interval '1 ms'
FROM mytable;
See the manual here.
回答by diego matos - keke
For milliseconds
毫秒
SELECT timestamp 'epoch' + proyecto.fecha_inicio * interval '1 ms'
from proyecto.proyecto
where proyecto.fecha_inicio is not null
For seconds
几秒钟
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
In the manual : http://www.postgresql.org/docs/current/interactive/functions-datetime.html.
在手册中：http: //www.postgresql.org/docs/current/interactive/functions-datetime.html。
Line: .. "Here is how you can convert an epoch value back to a time stamp"..
行：..“这是将纪元值转换回时间戳的方法”..
回答by Vka
Original question was related to Date data type, but all the answers so far relate to Timestamp data type.
原始问题与 Date 数据类型有关，但到目前为止所有答案都与 Timestamp 数据类型有关。
One way to convert milliseconds to Date would be:
将毫秒转换为日期的一种方法是：
SELECT DATE(any_time_field_containing_milliseconds/ 1000) FROM mytable;
This seems to use the timezone defined for database
这似乎使用为数据库定义的时区
