postgresql 将数字转换为日期和格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26198358/
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
postgresql cast numeric to date and format
提问by Arif
I have my date field as numeric in postgresql and the values in the date are like 1401710352000 and when I try to convert it using to_timestamp(date) then I get the corresponding timestamp as "46388-06-07 10:40:00+00"
我的日期字段在 postgresql 中为数字，日期中的值类似于 1401710352000，当我尝试使用 to_timestamp(date) 将其转换时，我得到相应的时间戳为“46388-06-07 10:40:00+00 ”
I have tried to google it and I have found quite useful functions on how to do different type conversions in postgresql but i could not find how to cast a numeric date to a proper readable format. i will apprecaite if someone can tell me how to convert a numeric date field to a timestamp/date readable format
我试图用谷歌搜索它，我发现了关于如何在 postgresql 中进行不同类型转换的非常有用的函数，但我找不到如何将数字日期转换为适当的可读格式。如果有人能告诉我如何将数字日期字段转换为时间戳/日期可读格式，我会很感激
Shah
沙阿
回答by mu is too short
From the fine manual:
从精美的手册：
to_timestamp(double precision)
timestamp with time zone
convert Unix epoch to time stamp
to_timestamp(double precision)
带时区的时间戳
将 Unix 纪元转换为时间戳
A Unix epoch is in seconds but it looks like your numeric value is in milliseconds. If we fix the units, then we get something that looks reasonable:
Unix 纪元以秒为单位，但看起来您的数值以毫秒为单位。如果我们固定单位，那么我们会得到一些看起来合理的东西：
=> select to_timestamp(1401710352000::numeric/1000);
to_timestamp
------------------------
2014-06-02 04:59:12-07
So you probably just need to fix the units by dividing by 1000 before you call to_timestamp.
因此，您可能只需要在调用to_timestamp.
回答by adelagd
I had the field 'fecha' in seconds.. and I had to converted it to date to be able to compare it (formatting it). I did it like that this:
我在几秒钟内就有了“fecha”字段......我必须将它转换为日期才能比较它（格式化它）。我是这样做的：
select to_char(to_timestamp(fecha),'DD/MM/YYYY') from...
