如何将时间戳转换为VARCHAR并在红移中保存到另一个表中？

Question

我有一个表，有一些如下的值，

CREATE TABLE school_1__test.tstamp(
    timeofday TIMESTAMP,
    timeofdaytz TIMESTAMP
);

insert into school_1__test.tstamp values('Jun 1,2008  09:59:59', 'Jun 1,2008 09:59:59 EST' );
insert into school_1__test.tstamp values('Dec 31,2008 18:20','Dec 31,2008 18:20');
insert into school_1__test.tstamp values('Jun 1,2008  09:59:59 EST', 'Jun 1,2008 09:59:59');

select * from school_1__test.tstamp;

-----------------------------------------------
     timeofday        |      timeofdaytz
-----------------------------------------------
2008-06-01 09:59:59.0 | 2008-06-01 09:59:59.0
2008-12-31 18:20:00.0 | 2008-12-31 18:20:00.0
2008-06-01 09:59:59.0 | 2008-06-01 09:59:59.0

CREATE TABLE school_1__test.date_test(
    timeofday VARCHAR
);

INSERT INTO school_1__test.date_test (
    select to_char(timeofday, 'YYYY-MM-DD HH:MI:SS')
    --select convert(timestamp, timeofday)
    --select cast(timeofday as timestamp)
    --select to_timestamp("timeofday",'YYYY-MM-DD HH:MI:SS')
    --select timeofday::timestamp
    --select to_date(timeofday,'YYYY-MM-DD HH:MI:SS')
    --select convert(varchar, timeofday::timestamp)
    --select convert(varchar, to_timestamp("timeofday",'YYYY-MM-DD HH:MI:SS'))
    from school_1__test.tstamp
);

select * from school_1__test.date_test;

----------
timeofday
----------
2008-06-01
2008-06-01
2008-12-31

select to_char(timeofday, 'YYYY-MM-DD HH:MI:SSTZ')
--select convert(timestamp, timeofday)
--select cast(timeofday as timestamp)
--select to_timestamp("timeofday",'YYYY-MM-DD HH:MI:SS')
--select timeofday::timestamp
--select to_date(timeofday,'YYYY-MM-DD HH:MI:SS')
--select convert(varchar, timeofday::timestamp)
--select convert(varchar, to_timestamp("timeofday",'YYYY-MM-DD HH:MI:SS'))
from school_1__test.tstamp

-------------------
      to_char
-------------------
2008-06-01 09:59:59
2008-12-31 06:20:00
2008-06-01 09:59:59

当使用insert命令时，获得date和time部分的解决方案是什么？

Answer 1

我在Amazon Redshift中使用Amazon Redshift控制台中的查询编辑器运行良好。

我使用了与您完全相同的命令（没有模式名）。