将datetime列转换为utc时间 [英] Convert datetime column to utc time

问题描述

我想实现以下目标.

我有一个使用以下函数计算的日期时间列.

I have a datetime column that i calculated with the following functions.

to_char(to_date(f_sta_date, 'YYYYMMDD') + (f_sta_time)/86400), 'YYYY-MM-DD HH24:MI:SS')
to_char(to_date(f_sta_date, 'YYYYMMDD') + (f_sta_time)/86400), 'YYYY-MM-DD HH24:MI:SS')

T1

f_sta_date f__sta_time f_sto_date  f_sto_time DT_sta              DT_sto
20191001   6689        20191001    7185       2019-10-01 01:54:49 2019-10-01 01:59:45

所需表

T1

f_sta_date f_sta_time_id f_sto_date f_sto_time_id DT_sta              DT_sto               DT_sta_UTC  DT_sto_UTC
20191001   6689          20191001   7185          2019-10-01 01:54:49 2019-10-01 01:59:45

我可以在上面的functions中添加什么功能以获得所需的结果?

What function could i add to the functions above to achieve the desired result?

推荐答案

从帖子下方评论中的讨论中可以看出，日期时间(您已经可以计算出)应该改为时间戳记时区，特别是时区CET".出于您的目的，也许带有时区的 date 就足够了(在Oracle中，"date"始终表示日期时间)； ,， Oracle没有这样的数据类型，我们必须使用带有时区的 timestamp .

From the discussion in comments under your post, it appears that your date-time (which you are already able to calculate) should instead be a timestamp with time zone, specifically "at time zone CET". Perhaps for your purposes date with time zone would suffice (in Oracle, "date" always means date-time); alas, Oracle doesn't have such a data type, we must use timestamp with time zone.

在下面的WITH子句中，我将演示如何计算该值. (请注意，我使用了另一种方法来计算日期时间，即使用专门为此目的设计的Oracle函数而不是算术运算，将时间"组件直接转换为秒.而且，尽管Oracle通常允许您使用一个数字-您的 date 组件-好像它是一个字符串，在某些情况下不是这样，例如，您可以在TO_DATE中执行此操作，但不能在TO_TIMESTAMP中执行此操作在任何情况下，您都从不依赖此类隐式转换；请始终在代码中将其明确显示.)

Below I show how to calculate that, in the WITH clause. (Note that I use a different approach for computing even the date-time - I convert your "time" component to seconds directly, using the Oracle function specifically designed for that, instead of arithmetic operations. Also, while Oracle usually allows you to use a number - your date component - as if it were a string, in some cases it doesn't. For example, you can do that in TO_DATE, but you can't in TO_TIMESTAMP. In any case, you should never rely on such implicit conversions; always make them explicit in your code.)

我展示了如何创建用于测试的表；然后，在查询中的WITH子句中，进行初步计算(计算DT_STA和DT_STO，但作为时间戳记带时区)；然后在主查询中，我展示了如何转换为其他时区.

I show how I created the table for testing; then, in the query, in the WITH clause I do a preliminary computation (computing your DT_STA and DT_STO, but as timestamps with time zone); then in the main query I show how to convert to a different time zone.

create table t1 (
  f_sta_date number, 
  f_sta_time number,
  f_sto_date number,
  f_sto_time number
);
insert into t1 values (20191001, 6689, 20191001, 7185);
commit;

with
  prep (f_sta_date, f_sta_time, f_sto_date, f_sto_time, dt_sta, dt_sto) as (
    select f_sta_date, f_sta_time, f_sto_date, f_sto_time,
           from_tz(to_timestamp(to_char(f_sta_date), 'yyyymmdd') 
                   + numtodsinterval(f_sta_time, 'second'), 'CET'),
           from_tz(to_timestamp(to_char(f_sto_date), 'yyyymmdd')
                   + numtodsinterval(f_sto_time, 'second'), 'CET')
    from   t1       
  )
select f_sta_date, f_sta_time, f_sto_date, f_sto_time, dt_sta, dt_sto,
       dt_sta at time zone 'UTC' as dt_sta_utc,
       dt_sto at time zone 'UTC' as dt_sto_utc
from   prep
;

F_STA_DATE F_STA_TIME F_STO_DATE F_STO_TIME DT_STA                  DT_STO                  DT_STA_UTC              DT_STO_UTC             
---------- ---------- ---------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
  20191001       6689   20191001       7185 2019-10-01 01:51:29 CET 2019-10-01 01:59:45 CET 2019-09-30 23:51:29 UTC 2019-09-30 23:59:45 UTC

这篇关于将datetime列转换为utc时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！