时间戳

首页 > 解决方案 > Oracle - 将 Unixtime 转换为本地日期时间，包括 DST（夏令时），反之亦然

sql - Oracle - 将 Unixtime 转换为本地日期时间，包括 DST（夏令时），反之亦然

问题描述

我有一个NUMBER field带有 UnixTimestamps 的（UTCSTAMP），我想要一个（自定义）函数来轻松返回本地日期时间（欧洲/阿姆斯特丹），包括 DST（夏令时）。输出应该是这种格式：'yyyy-mm-dd hh24:mi:ss' 例如：unix_ts2date_function(1576666800)

我找到了这篇文章：Convert Unixtime to Datetime SQL (Oracle)但它没有考虑夏令时。

我还想要一个（自定义）函数来轻松地将包括 DST（夏令时）在内的本地日期时间（欧洲/阿姆斯特丹）转换为 UnixTimestamp 并在 WHERE 子句中使用它。例如：

SELECT * FROM table
WHERE UTCSTAMP > date2unix_ts_function('2019-05-01') (DST is active)
AND UTCSTAMP < date2unix_ts_function('2020-11-30') (DST not active)

我还发现了这篇文章：Convert timestamp datatype into unix timestamp Oracle但它也没有考虑夏令时。

标签： sqloracleunix-timestamp

解决方案

将utcstamp秒数添加到纪元1970-01-01 UTC（作为TIMESTAMP数据类型），然后用于AT TIME ZONE将其转换为所需的时区：

甲骨文设置：

CREATE TABLE your_table ( utcstamp ) AS
SELECT 1576666800 FROM DUAL

查询：

SELECT ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
         AT TIME ZONE 'Europe/Amsterdam' AS Amsterdam_Time
FROM   your_table

输出：

| AMSTERDAM_TIME |
| :------------------------------------------------------------ |
| 2019-12-18 12:00:00.000000000 欧洲/阿姆斯特丹 |

查询 2：

如果你想要它，DATE那么只需将所有内容包装在 a 中CAST：

SELECT CAST(
         ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utcstamp * INTERVAL '1' SECOND )
           AT TIME ZONE 'Europe/Amsterdam'
         AS DATE
       ) AS Amsterdam_Time
FROM   your_table

输出：

| AMSTERDAM_TIME |
| :----------------- |
| 2019-12-18 12:00:00 |

db<>在这里摆弄

CREATE FUNCTION DATE_TO_UTCEPOCHTIME (
  dt IN DATE,
  tz IN VARCHAR2 DEFAULT 'UTC'
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN ROUND(
           ( CAST( FROM_TZ( dt, tz ) AT TIME ZONE 'UTC' AS DATE )
             - DATE '1970-01-01' )
           * 24 * 60 * 60
         );
END;
/

CREATE FUNCTION UTCEPOCHTIME_TO_DATE(
  utctime IN NUMBER,
  tz      IN VARCHAR2 DEFAULT 'UTC'
) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN ( TIMESTAMP '1970-01-01 00:00:00 UTC' + utctime * INTERVAL '1' SECOND )
           AT TIME ZONE tz;
END;
/

那么你可以这样做：

SELECT utcepochtime_to_date( utcstamp, 'Europe/Amsterdam' )
FROM   your_table;

哪个输出：

| UTCEPOCHTIME_TO_DATE(UTCSTAMP,'EUROPE/AMSTERDAM') |
| :------------------------------------------------ |
| 2019-12-18 12:00:00 |

和

SELECT date_to_utcepochtime(
         DATE '2019-12-18' + INTERVAL '12:00:00' HOUR TO SECOND,
         'Europe/Amsterdam'
       ) AS utcepochtime
FROM   DUAL;

输出：

| UTCEPOCHTIME |
| ------------: |
| 1576666800 |

db<>在这里摆弄

推荐阅读