SQL 如何将大纪元时间转换为日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23289218/
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 convert Epoch time to date?
提问by pradeep kumar
Hi I have a column with number datatype the data like 1310112000this is a date, but I don't know how to make it in an understandable format: ex: 10-mar-2013 12:00:00 pm
嗨，我有一个数字数据类型的列，数据像 1310112000，这是一个日期，但我不知道如何以可理解的格式制作它：例如：10-mar-2013 12:00:00 pm
Can any one please help me.
谁能帮帮我吗。
采纳答案by MinhD
That is EPOCH time: number of seconds since Epoch(1970-01-01). Use this:
那是 EPOCH 时间：自 Epoch(1970-01-01) 以来的秒数。用这个：
SELECT CAST(DATE '1970-01-01' + ( 1 / 24 / 60 / 60 ) * '1310112003' AS TIMESTAMP) FROM DUAL;
Result:
结果：
08-JUL-11 08.00.03.000000000 AM
回答by rahulnikhare
Please try
请尝试
select from_unixtime(floor(EPOCH_TIMESTAMP/1000)) from table;
This will give the result like E.g: 2018-03-22 07:10:45 PFB refence from MYSQL
这将给出像 Eg: 2018-03-22 07:10:45 PFB refence from MYSQL 这样的结果
回答by Mike Finch
In Microsoft SQL Server, the previous answers did not work for me. But the following does work.
在 Microsoft SQL Server 中，以前的答案对我不起作用。但以下确实有效。
SELECT created_time AS created_time_raw,
dateadd( second, created_time, CAST( '1970-01-01' as datetime ) ) AS created_time_dt
FROM person
personis a database table, and created_timeis an integer field whose value is a number of seconds since epoch.
person是一个数据库表，并且created_time是一个整数字段，其值为自纪元以来的秒数。
There may be other ways to do the datetimearithmetic. But this is the first thing that worked. I do not know if it is MSSQL specific.
可能还有其他方法可以进行datetime算术运算。但这是第一件事。我不知道它是否特定于 MSSQL。
