SQL 如何在 Oracle 中将日期截断为秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/158189/
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 trunc a date to seconds in Oracle
提问by Zizzencs
回答by Tony Andrews
Since the precision of DATEis to the second (and no fractions of seconds), there is no need to TRUNCat all.
由于 的精度DATE是秒（而不是秒的小数部分），因此根本不需要TRUNC。
The data type TIMESTAMPallows for fractions of seconds. If you convert it to a DATEthe fractional seconds will be removed - e.g.
数据类型TIMESTAMP允许几分之一秒。如果您将其转换为 aDATE小数秒将被删除 - 例如
select cast(systimestamp as date)
from dual;
回答by bierwaermer
I am sorry, but all my predecessors seem to be wrong.
对不起，但我所有的前辈似乎都错了。
select cast(systimestamp as date) from dual
..does not truncate, but rounds to the next second instead.
.. 不截断，而是舍入到下一秒。
I use a function:
我使用一个函数：
CREATE OR REPLACE FUNCTION TRUNC_TS(TS IN TIMESTAMP) RETURN DATE AS
BEGIN
RETURN TS;
END;
For example:
例如：
SELECT systimestamp
,trunc_ts(systimestamp) date_trunc
,CAST(systimestamp AS DATE) date_cast
FROM dual;
Returns:
返回：
SYSTIMESTAMP DATE_TRUNC DATE_CAST
21.01.10 15:03:34,567350 +01:00 21.01.2010 15:03:34 21.01.2010 15:03:35
回答by drnk
I used function like this:
我使用了这样的功能：
FUNCTION trunc_sec(p_ts IN timestamp)
IS
p_res timestamp;
BEGIN
RETURN TO_TIMESTAMP(TO_CHAR(p_ts, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI');
END trunc_sec;
回答by David Aldridge
On the general topic of truncating Oracle dates, here's the documentation link for the format models that can be used in date trunc() AND round() functions
关于截断 Oracle 日期的一般主题，这里是可在日期 trunc() 和 round() 函数中使用的格式模型的文档链接
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions242.htm#sthref2718
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions242.htm#sthref2718
"Seconds" is not listed because the granularity of the DATE datatype is seconds.
未列出“秒”，因为 DATE 数据类型的粒度为秒。
回答by David
To truncate a timestampto seconds you can cast it to a date:
要将 a 截断timestamp为秒，您可以将其转换为日期：
CAST(timestamp AS DATE)
To then perform the TRUNC's in the article:
然后执行TRUNC文章中的's：
TRUNC(CAST(timestamp AS DATE), 'YEAR')
回答by EvilTeach
Something on the order of:
顺序如下：
select to_char(current_timestamp, 'SS') from dual;
回答by andy
trunc work to min only, cast to date to_char(START_TIME,'YYYYMMDDHH24MISS')
trunc 工作到 min，投至今日 to_char(START_TIME,'YYYYMMDDHH24MISS')
or simply select to_char(current_timestamp, 'YYYYMMDDHH24MISS') from dual;
或者干脆 select to_char(current_timestamp, 'YYYYMMDDHH24MISS') from dual;
https://www.techonthenet.com/oracle/functions/trunc_date.php
https://www.techonthenet.com/oracle/functions/trunc_date.php
