oracle 截断时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5099080/
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
Truncating timestamps
提问by zerkms
Let's suppose I have a timestamp variable:
假设我有一个时间戳变量：
select timestamp '2011-02-24 08:30:42 +06:00' from dual;
Is there any way to "truncate" it to something like
有没有办法将它“截断”为类似的东西
'2011-02-24 08:00:00 +06:00'
(I've cut minutes and seconds, but left the timezone)
（我已经减少了分秒，但离开了时区）
The target oracle version is 11g r2
目标oracle版本为11g r2
采纳答案by Shannon Severance
SQL> select to_timestamp_tz(to_char(timestamp '2011-02-24 08:30:42 +06:00', 'YYYY-MM-DD HH24 TZH:TZM'), 'YYYY-MM-DD HH24 TZH:TZM') from dual;
TO_TIMESTAMP_TZ(TO_CHAR(TIMESTAMP'2011-02-2408:30:42+06:00','YYYY-MM-DDTZH:
---------------------------------------------------------------------------
24.02.2011 8:00:00,000000000 +06:00
回答by OMG Ponies
I got it to work using:
我让它工作使用：
SELECT TO_TIMESTAMP_TZ(TO_CHAR(timestamp '2011-02-24 08:30:42 +06:00', 'YYYY-MM-DD HH24') || ':00:00 '|| TO_CHAR(timestamp '2011-02-24 08:30:42 +06:00', 'TZH:TZM'), 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
FROM DUAL
TRUNCsupports timestamps 9.2.0.3+, but in my testing on 10g Express Edition this
TRUNC支持时间戳 9.2.0.3+，但在我对 10g Express Edition 的测试中
SELECT TRUNC(timestamp '2011-02-24 08:30:42 +06:00', 'HH')
FROM DUAL
...totally screwed the hours and AM/PM. Didn't matter for a literal, or if supplied inside of TO_TIMESTAMP_TZ for me. The documentation for TRUNC(date) for 10gR2 says "The value returned is always of datatype DATE, even if you specify a different datetime datatype for date.".
...完全搞砸了时间和上午/下午。对于文字，或者是否在 TO_TIMESTAMP_TZ 内为我提供无关紧要。10gR2 的 TRUNC(date) 文档说“返回的值始终是数据类型 DATE，即使您为日期指定了不同的日期时间数据类型。”。
回答by borgille
Your NLS_DATE_FORMAT is probably set to display only the date portion. This works for me:
您的 NLS_DATE_FORMAT 可能设置为仅显示日期部分。这对我有用：
SELECT
to_char( TRUNC(timestamp'2011-02-24 08:30:42 +06:00', 'HH'), 'YYYY-MM-DD HH24:MI:SS' )
FROM DUAL;
Keep in mind that the result returned is a DATE, so you lose any localization info. You can return a GMT-normalized timestamp by casting the result to a timestamp:
请记住，返回的结果是 DATE，因此您会丢失任何本地化信息。您可以通过将结果转换为时间戳来返回 GMT 标准化时间戳：
SELECT
to_char( from_tz( cast( TRUNC(timestamp'2011-02-24 08:30:42 +06:00' at time zone 'GMT', 'HH' ) as timestamp ), 'GMT' ), 'YYYY-MM-DD HH24:MI:SS TZR' )
FROM DUAL;
To preserve the timezone info you have to do something pretty convoluted. It might be easier to use a stored function to perform the conversion at that point. This works much like the Oracle built-in TRUNC() function:
要保留时区信息，您必须做一些非常复杂的事情。此时使用存储函数执行转换可能更容易。这很像 Oracle 内置的 TRUNC() 函数：
create or replace function trunc_timestamp(
ts in timestamp_tz_unconstrained,
fmt in varchar2
)
return timestamp_tz_unconstrained
is
tzone varchar2(20);
begin
tzone := extract( timezone_region from ts );
if tzone = 'UNKNOWN' then
tzone := to_char( extract( timezone_hour from ts ), 'fm09' )
|| ':'
|| to_char( extract( timezone_minute from ts ), 'fm09' );
end if;
return from_tz( cast( TRUNC( ts at time zone tzone, fmt ) as timestamp ), tzone );
end;
/
I created a sqlfiddlefor a demo.
我为演示创建了一个sqlfiddle。
回答by maks
Try select cast(timestamp as date) from dual
尝试 select cast(timestamp as date) from dual
