Oracle SQL - 带有 unix 时间戳的列，需要 dd-mm-yyyy 时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2401396/
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
Oracle SQL - Column with unix timestamp, need dd-mm-yyyy timestamp
提问by opHASnoNAME
is there any way in Oracle, to get only the dd-mm-yyyy part from an unix timestamp in oracle?
在 Oracle 中有什么方法可以从 oracle 的 unix 时间戳中只获取 dd-mm-yyyy 部分吗？
Like:
喜欢：
select to_char(my_timestamp, 'ddmmyyyy') as my_new_timestamp from table
采纳答案by APC
Given this data ...
鉴于这些数据...
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
2 /
Session altered.
SQL> select * from t23
2 /
MY_TIMESTAMP
--------------------
08-mar-2010 13:06:02
08-mar-2010 13:06:08
13-mar-1985 13:06:26
SQL>
.. it is simply a matter of converting the time elapsed since 01-JAN-1970into seconds:
.. 这只是将自 01-JAN-1970 以来经过的时间转换为秒的问题：
SQL> select my_timestamp
2 , (my_timestamp - date '1970-01-01') * 86400 as unix_ts
3 from t23
4 /
MY_TIMESTAMP UNIX_TS
-------------------- ----------
08-mar-2010 13:06:02 1268053562
08-mar-2010 13:06:08 1268053568
13-mar-1985 13:06:26 479567186
SQL>
回答by Tony Andrews
As I understand it, A Unix timestamp is defined as a number of seconds since 1970-01-01, in which case:
据我了解，Unix 时间戳定义为自 1970-01-01 以来的秒数，在这种情况下：
select DATE '1970-01-01' + my_timestamp/86400 from table;
(There are 86400 seconds in a day.)
（一天有 86400 秒。）
To ignore the hours, minutes and seconds:
要忽略小时、分钟和秒：
select TRUNC(DATE '1970-01-01' + my_timestamp/86400) from table;
However, if what you want is a "truncated" Unix timestamp then try this:
但是，如果您想要的是“截断”的 Unix 时间戳，请尝试以下操作：
select floor(my_timestamp/84600)*84600 from dual;
回答by Matthew Flaschen
I believe it's:
我相信是：
select to_char(my_timestamp, 'dd-mm-yyyy') as my_new_timestamp from table
See also this referenceon Oracle's date format specifiers.
另请参阅有关 Oracle 日期格式说明符的参考资料。
