'列的类型为时间戳，没有时区，但表达式的类型为时间间隔'Redshift [英] 'Column is of type timestamp without time zone but expression is of type interval' Redshift

问题描述

我有一列时区名称.我想获得另一列，其中包含与UTC时间相比的时差.但是我不知道在创建表时应该为该偏移量列使用哪种数据类型.我用过:

I have one column of timezone names. I want to get another column with the time difference compared to UTC time. But I don't know which data type I should use for this offset column when creating the table. I used:

CREATE TABLE zone_offset(
zone_name varchar(50),
zone_utc_diff timestamp
);

INSERT INTO zone_offset

SELECT zone_name, getdate() - getdate() at time zone zone_name AS zone_utc_diff
FROM zones

，我得到了那个错误.我该如何使其与任何数据类型一起使用?

and I got that error. How can I make it work with whatever datatype?

非常感谢您！

推荐答案

您应该将zone_utc_diff存储为间隔而不是时间戳.时间戳代表日期/时间，而间隔代表日期之间的差异.

You should probably store zone_utc_diff as an interval, not as a timestamp. Timestamps represent date/times, while intervals represent differences between dates.

DB Fiddle上的演示 :

Demo on DB Fiddle:

create table zones (
    zone_name varchar(50)  
);    

insert into zones values('cet');

create table zone_offset(
    zone_name varchar(50),
    zone_utc_diff interval
);

insert into zone_offset
select zone_name, current_timestamp - (current_timestamp at time zone zone_name) as zone_utc_diff
from zones


select * from zone_offset;

| zone_name | zone_utc_diff   |
| --------- | --------------- |
| cet       | {"hours":-1}    |

这篇关于'列的类型为时间戳，没有时区，但表达式的类型为时间间隔'Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！