SQL 如何在 Postgresql 中将 bigint 字段格式化为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5485502/
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 format bigint field into a date in Postgresql?
提问by Stephan
I have a table with a field of type bigint. This field store a timestamp. I want to date format the field like this :
我有一个带有 bigint 类型字段的表。该字段存储时间戳。我想像这样对字段进行日期格式：
to_char( bigint_field,'DD/MM/YYYY HH24:MI:SS')
I get the following error :
我收到以下错误：
ERROR: multiple decimal points état SQL :42601
回答by Quassnoi
TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')
回答by Jordan K
This depends on what the bigint value represents - offset of epoch time, or not.
这取决于 bigint 值代表什么 - 纪元时间的偏移量，与否。
select to_timestamp(20120822193532::text, 'YYYYMMDDHH24MISS')
returns
回报
"2012-08-22 19:35:32+00"
“2012-08-22 19:35:32+00”
回答by Th3NetForc3
I did it like this:
我是这样做的：
to_timestamp(to_char(20120822193532, '9999-99-99 99:99:99'),'YYYY-MM-DD HH24:MI:SS')the result looks like this:
结果如下所示：
2012-08-22 19:35:32
2012-08-22 19:35:32
you also can use this in you select statemant, just exchange the number with your database colunm.
您也可以在选择语句中使用它，只需将数字与数据库列交换即可。
Step by Step Explanation:
分步说明：
to_char(20120822193532, '9999-99-99 99:99:99')This will create a string like this:
这将创建一个这样的字符串：
"2012-08-22 19:35:32"
“2012-08-22 19:35:32”
now we can easiely convert this into a timestamp:
现在我们可以轻松地将其转换为时间戳：
to_timestamp('2012-08-22 19:35:32','YYYY-MM-DD HH24:MI:SS')Result will look the same as before, but it's now a timestamp.
结果看起来和以前一样，但它现在是一个时间戳。
Also, if you use this for a command like
此外，如果您将其用于类似的命令
CREATE TABLE table2 AS SELECT to_timestamp(to_char(tb1.date, '9999-99-99 99:99:99'),'YYYY-MM-DD HH24:MI:SS') AS realDate FROM table1 AS tb1; you might end up with timstamptz(timestamp withtime zone) instead of timestamp(timestamp withouttime zone). You can change it like this:
您最终可能会得到timstamptz（带时区的时间戳）而不是时间戳（不带时区的时间戳）。你可以像这样改变它：
ALTER TABLE table2 ALTER realDate SET DATA TYPE timestamp USING realDate;