截断时间戳

发布于 2024-10-19 02:36:19 字数 239 浏览 5 评论 0原文

假设我有一个时间戳变量：

select timestamp '2011-02-24 08:30:42 +06:00' from dual;

有什么方法可以将其“截断”为类似的内容

'2011-02-24 08:00:00 +06:00'

（我已经削减了分钟和秒，但保留了时区）

目标oracle版本是11g r2

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏日落 2024-10-26 02:36:19

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

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

回复收藏 0 原文

Bonjour°[大白 2024-10-26 02:36:19

我让它工作使用：

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

TRUNC 支持时间戳 9.2.0.3+，但在我对 10g Express Edition 的测试中，这

SELECT TRUNC(timestamp '2011-02-24 08:30:42 +06:00', 'HH')
  FROM DUAL

...完全搞砸了时间和上午/下午。对于文字来说并不重要，或者如果在 TO_TIMESTAMP_TZ 内提供的话对我来说并不重要。 10gR2 的 TRUNC(date) 文档表示“返回的值始终是 DATE 数据类型，即使您为 date 指定了不同的日期时间数据类型。”。

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

TRUNC supports timestamps 9.2.0.3+, but in my testing on 10g Express Edition this

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.".

回复收藏 0 原文

蓝眼泪 2024-10-26 02:36:19

您的 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;

请记住，返回的结果是日期，因此您会丢失任何本地化信息。您可以通过将结果转换为时间戳来返回 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;

要保留时区信息，您必须执行一些非常复杂的操作。此时使用存储函数来执行转换可能会更容易。这与 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;
/

我创建了一个 sqlfiddle 用于演示。

Your NLS_DATE_FORMAT is probably set to display only the date portion. This works for me:

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:

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:

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 sqlfiddle for a demo.

回复收藏 0 原文