How to convert TIMESTAMP into VARCHAR and save it into another table in Redshift?

Question

I have a table and have some values like below,

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

Now I have another table like below,

CREATE TABLE school_1__test.date_test(
    timeofday VARCHAR
);

I want to insert data from tstamp table to date_test table and tried with each and every command(commented) in below statement,

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
);

But when I see the table data it displays only DATE details, cannot find TIME details.

select * from school_1__test.date_test;

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

Before before conversion I checked each and every SELECT statements, it displays the correct output.

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

I checked How to Insert TIMESTAMP Column into Redshift issue, but I don't use any COPY command here and all the data inserted by myself, so nothing get anything from S3.

What is the solution to get both DATE and TIME parts when using that INSERT command?

thank you @Jasen, just change. but the problem is same as before. — tikirin tukurun, Dec 15 '20 at 23:37
hmm it's working when I try it in postgresql. to me it looks like a redshift bug. — Jasen, Dec 15 '20 at 23:45
Thank you so much @Jasen for your time and effort, I tried with `DBeaver`. It's working perfectly in Amazon Redshift console, but it's very slow. Anyway I won't use `DBeaver` anymore. In `DBeaver` every time we have to sync after data insertion, it's useless. — tikirin tukurun, Dec 16 '20 at 01:37

score 2 · Accepted Answer · answered Dec 16 '20 at 00:50

2

It worked fine for me in Amazon Redshift, using the Query Editor in the Amazon Redshift console.

I used your exact commands (without the schema name).

answered Dec 16 '20 at 00:50

John Rotenstein

241,921
22
380
470

Thank you so much @john-rotenstein for your time and effort, I tried with `DBeaver`. It's working perfectly in Amazon Redshift console, but it's very slow. Anyway I won't use `DBeaver` anymore. In `DBeaver` every time we have to sync after data insertion, it's useless. – tikirin tukurun Dec 16 '20 at 01:35
1

I know that some SQL clients can modify results and cause confusing output! I like using DbVisualizer -- it seems to know every database that's out there. – John Rotenstein Dec 16 '20 at 02:09

How to convert TIMESTAMP into VARCHAR and save it into another table in Redshift?

1 Answers1