Oracle 如何查询日期值并在查询值前 5 小时返回值

Question

我想查询以下列，其中包含"具有本地时区的时间戳"类型的列。

出于某种原因(这并不那么相关，因为这将解决我的目的方面的问题(每次我插入该列时，我都会得到一个值 5 小时。我不在乎，那么多，如果我能得到从我的SQL返回的真实时间。

我正在尝试这样的事情：

SELECT to_char(start_time, 'MM-DD-YYYY HH24:MI' ) s_time - (5/24) from table; 

我不断收到指示我正在使用冲突数据类型的错误。

任何人都可以解释一下我如何在输出中返回的列中的实际值之前 5 小时获得一个值？我已经为此苦苦挣扎了一段时间。

谢谢。

Answer 1

仅解决您的错误，您需要在转换为字符串之前减去五个小时：

SELECT to_char(start_time - (5/24), 'MM-DD-YYYY HH24:MI') s_time from table;

或带有间隔而不是小数天(将其保留为时间戳(：

SELECT to_char(start_time - interval '5' hour, 'MM-DD-YYYY HH24:MI') s_time from table;

演示：

create table t1 (col1 timestamp with local time zone);
insert into t1 (col1) values (timestamp '2019-09-01 00:00:00 UTC');
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF1 TZH:TZM';
alter session set time_zone = 'Europe/London';
select col1, col1 - 5/24 as col1_adj1, col1 - interval '5' hour as col1_adj2
from t1;
COL1                  COL1_ADJ1             COL1_ADJ2
--------------------- --------------------- ---------------------
2019-09-01 01:00:00.0 2019-08-31 20:00:00   2019-08-31 20:00:00.0
alter session set time_zone = 'America/New_York';
select col1, col1 - 5/24 as col1_adj1, col1 - interval '5' hour as col1_adj2
from t1;
COL1                  COL1_ADJ1             COL1_ADJ2
--------------------- --------------------- ---------------------
2019-08-31 20:00:00.0 2019-08-31 15:00:00   2019-08-31 15:00:00.0

至于为什么首先是(或看起来是(5小时，因为列数据类型是本地时区，你会看到会话时区中存储的值。可能只是您希望在服务器时区中看到它。比较您从systimestamp和current_timestamp中看到的值 - 它们也可能相隔五个小时。

一些示例查询：

手动调整时间可能不是一个好主意。如果必须，那么最好使用at time zone进行调整，因为如有必要，这将处理 DST。

alter session set time_zone = 'Europe/London';
select col1,
col1 at time zone 'Africa/Nairobi' as col1_adj3,
to_char(col1 at time zone 'Africa/Nairobi', 'YYYY-MM-DD HH24:MI:SS') as col1_adj4
from t1;
COL1                  COL1_ADJ3                      COL1_ADJ4
--------------------- ------------------------------ -------------------
2019-09-01 01:00:00.0 2019-09-01 03:00:00.0 +03:00   2019-09-01 03:00:00
alter session set time_zone = 'America/New_York';
select col1,
col1 at time zone 'Africa/Nairobi' as col1_adj3,
to_char(col1 at time zone 'Africa/Nairobi', 'YYYY-MM-DD HH24:MI:SS') as col1_adj4
from t1;
COL1                  COL1_ADJ3                      COL1_ADJ4
--------------------- ------------------------------ -------------------
2019-08-31 20:00:00.0 2019-09-01 03:00:00.0 +03:00   2019-09-01 03:00:00

现在，无论您的本地会话设置如何，结果都是相同的。

Answer 2

[TL;DR]使用start_time AT TIME ZONE 'PST'(或任何适合您的用户的时区(或start_time AT LOCAL，而不是增加或减去小时数。

甲骨文设置：

CREATE TABLE table_name ( start_time TIMESTAMP WITH LOCAL TIME ZONE );
-- Change the session time zone:
ALTER SESSION SET TIME_ZONE = 'PST';
-- Use ISO 8601 formatting for displaying the timestamps
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY-MM-DD"T"HH24:MI:SS.FF3';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZH:TZM';
-- Insert some data.
-- The first row uses the local time zone
-- The second row explicitly specifies the time zone as UTC+01:00
INSERT INTO table_name ( start_time )
SELECT TIMESTAMP '2019-09-17 00:00:00'        FROM DUAL UNION ALL
SELECT TIMESTAMP '2019-09-17 00:00:00 +01:00' FROM DUAL;

查询：

现在，如果我们更改用户的时区：

ALTER SESSION SET TIME_ZONE = 'UTC';

并获取表中的值：

SELECT start_time,
start_time AT TIME ZONE 'PST' AS start_time_pst,
start_time AT LOCAL           AS start_time_local
FROM   table_name;

这输出：

START_TIME |START_TIME_PST |START_TIME_LOCAL              :---------------------- |:----------------------------- |:----------------------------- 2019-09-17T07：00：00.000 |2019-09-17T00：00：00.000 -07：00 |2019-09-17T07：00：00.000 +00：00 2019-09-16T23：00：00.000 |2019-09-16T16：00：00.000 -07：00 |2019-09-16T23：00：00.000 +00：00

数据是从PST时区输入的，现在正在UTC时区进行查询，并且"神奇地"移动了 7 小时。好吧，这没有什么神奇的...这是时区偏移，默认情况下，它不会显示列的时区，除非明确说明您希望它位于特定时区。

如果要选择特定时区的数据，请使用AT TIME ZONE 'PST'或AT LOCAL(如果您希望使用会话的本地时区(。

如果我们再次更改会话的时区：

ALTER SESSION SET TIME_ZONE = 'EST';

并运行相同的查询，我们得到：

SELECT start_time,
start_time AT TIME ZONE 'PST' AS start_time_pst,
start_time AT LOCAL           AS start_time_local
FROM   table_name;

START_TIME |START_TIME_PST |START_TIME_LOCAL              :---------------------- |:----------------------------- |:----------------------------- 2019-09-17T02：00：00.000 |2019-09-17T00：00：00.000 -07：00 |2019-09-17T02：00：00.000 -05：00 2019-09-16T18：00：00.000 |2019-09-16T16：00：00.000 -07：00 |2019-09-16T18：00：00.000 -05：00

同样，您可以看到值已更改，但通过选择AT TIME ZONE 'PST'无论会话时区如何，您都会获得固定输出，并且AT LOCAL显示相同的时间(如果您调整其现在显示的时区(。

db<>小提琴在这里