红移时间数据类型 [英] Redshift Time Datatype
问题描述
我正在尝试创建和加载TIME列，与其他要存储的数据库不同，只有时间.即09:30:00，14:23:16根据此 https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html Redshift具有TIME数据类型.但是，尝试像日期和时间戳记数据类型一样使用它确实具有挑战性.
I am trying to create and load a TIME column not unlike other databases to store, only the time. i.e. 09:30:00, 14:23:16 According to this https://docs.aws.amazon.com/redshift/latest/dg/r_Datetime_types.html Redshift has a TIME data type. However trying to use it like the date and timestamp datatypes is proving challenging.
select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.18228
(1 row)
-- TimeStamps ------------------------------------------------------------------------
drop table if exists t1;
psql:redshiftTime.sql:4: INFO: Table "t1" does not exist and will be skipped
DROP TABLE
create table t1 (c1 timestamp);
CREATE TABLE
insert into t1 (select current_timestamp);
INSERT 0 1
insert into t1 values ('2020-03-01 07:30:57.123456');
INSERT 0 1
insert into t1 values ('2020-03-01 07:30:57');
INSERT 0 1
-- Dates -----------------------------------------------------------------------------
drop table if exists t1;
DROP TABLE
create table t1 (c1 date);
CREATE TABLE
insert into t1 (select current_date);
INSERT 0 1
insert into t1 values ('2020-03-01');
INSERT 0 1
-- Times -----------------------------------------------------------------------------
drop table if exists t1;
DROP TABLE
create table t1 (c1 time); -- FAILS????
psql:redshiftTime.sql:19: ERROR: Column "t1.c1" has unsupported type "time without time zone".
insert into t1 (select current_time);
psql:redshiftTime.sql:20: ERROR: relation "t1" does not exist
insert into t1 values ('12:00:00');
psql:redshiftTime.sql:21: ERROR: relation "t1" does not exist
-- CHAR Woraround ???? ----------------------------------------------------------------
drop table if exists t1;
psql:redshiftTime.sql:25: INFO: Table "t1" does not exist and will be skipped
DROP TABLE
create table t1 (c1 CHAR(8));
CREATE TABLE
insert into t1 (select current_time); -- FAILS - expected due to mismatch data type
psql:redshiftTime.sql:27: INFO: Function "text(time with time zone)" not supported.
psql:redshiftTime.sql:27: INFO: Function "timetz(time with time zone,integer)" not supported.
psql:redshiftTime.sql:27: ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.
insert into t1 values ('12:00:00'); -- OK
INSERT 0 1
SELECT CAST( current_time AS CHAR(8)); -- OK - automagic substring
bpchar
----------
08:42:24
(1 row)
insert into t1 (SELECT CAST( current_time AS CHAR(8))); -- FAILS
psql:redshiftTime.sql:31: INFO: Function "text(time with time zone)" not supported.
psql:redshiftTime.sql:31: INFO: Function "timetz(time with time zone,integer)" not supported.
psql:redshiftTime.sql:31: ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.
SELECT CAST( current_time AS CHAR(18)); -- OK
bpchar
--------------------
08:42:25.411191+00
(1 row)
insert into t1 (SELECT substr(CAST( current_time AS CHAR(18)),1,8)); -- FAILS
psql:redshiftTime.sql:34: INFO: Function "text(time with time zone)" not supported.
psql:redshiftTime.sql:34: INFO: Function "timetz(time with time zone,integer)" not supported.
psql:redshiftTime.sql:34: ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.
推荐答案
我的测试与您的发现相符.
My testing matches your findings.
但是，虽然我无法创建类型为 TIME 的列，但可以将其转换为TIME:
However, while I could not create a column of type TIME, I could cast to TIME:
SELECT NOW()::TIME
09:10:08
但是，我无法将 TIMESTAMP 表列强制转换为 TIME .最奇怪！
However, I was unable to cast a TIMESTAMP table column to TIME. Most strange!
这篇关于红移时间数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
