sql - Oracle如何查询日期值并返回查询值前5小时的值

Question

我想查询下面的列，其中包含“TIMESTAMP WITH LOCAL TIMEZONE”类型的列。

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

我正在尝试这样的事情：

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

我不断收到错误消息，表明我正在使用冲突的数据类型。

谁能解释一下我如何在输出中返回的列中的实际值之前 5 小时获得一个值？我已经和它搏斗了一段时间。

谢谢你。

标签： sqloracle

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

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