如何从 Oracle 中的时间戳中删除毫秒？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46927041/
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 can I remove milliseconds from Timestamp in Oracle?
提问by Thangaraj Murugananthan
I want to export data from 'My_Customer' table in .csv format.
I am extracting a time field but I am getting the output along with Milliseconds.
Eg:
The output I am getting,25-10-2017 12:53:12:00076
I want to remove/truncate the milliseconds.
我想以 .csv 格式从“My_Customer”表中导出数据。
我正在提取一个时间字段，但我正在获取输出和毫秒。
例如：
我得到的输出，25-10-2017 12:53:12:00076
我想删除/截断毫秒。
Expected Output-25-10-2017 12:53:12.
预期输出- 25-10-2017 12:53:12。
I have tried to_char()but it isn't giving proper output. (Instead of giving the exact time available in that field, it is rounding off).
我试过了，to_char()但没有给出正确的输出。（它没有给出该领域可用的确切时间，而是rounding off）。
回答by MT0
You can either cast it to a timestamp with no fractional seconds (this will round to the nearest second):
您可以将其转换为没有小数秒的时间戳（这将四舍五入到最接近的秒）：
CAST( your_timestamp AS TIMESTAMP(0) )
Or to a DATEdata type (this will truncate to the nearest second):
或DATE数据类型（这将截断到最接近的秒）：
CAST( your_timestamp AS DATE )
Or you can convert it to a formatted string and specify the format model you want to use (this will truncate to the nearest second):
或者您可以将其转换为格式化字符串并指定您要使用的格式模型（这将截断到最接近的秒）：
TO_CHAR( your_timestamp, 'YYYY-MM-DD HH24:MI:SS' )
Like this:
像这样：
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE your_table ( your_timestamp ) AS
SELECT TIMESTAMP '2017-10-25 12:53:12.10076' FROM DUAL;
Query 1:
查询 1：
SELECT CAST( your_timestamp AS TIMESTAMP(0) ) AS "Timestamp",
CAST( your_timestamp AS DATE ) AS "Date",
TO_CHAR( your_timestamp, 'DD-MM-YYYY HH24:MI:SS' ) AS "String"
FROM your_table
结果：
| Timestamp | Date | String |
|-----------------------|----------------------|---------------------|
| 2017-10-25 12:53:12.0 | 2017-10-25T12:53:12Z | 25-10-2017 12:53:12 |
How the TIMESTAMPand DATEare formatted will depend on your NLS_TIMESTAMP_FORMATand NLS_DATE_FORMATsession parameters but you can directly control the formatting of TO_CHARwhen you specify a format model.
如何TIMESTAMP和DATE格式化将取决于你NLS_TIMESTAMP_FORMAT和NLS_DATE_FORMAT会话参数，但可以直接控制的格式TO_CHAR，当你指定格式的模型。
回答by Cyrille MODIANO
Convert your timestamp to a character string:
将时间戳转换为字符串：
SQL> SELECT TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS') AS my_date
FROM DUAL;
MY_DATE
-----------------------------
2017-OCT-13 00:38:26
