CODEWITHSUNDEEP
sqlamazon-web-servicestimestampamazon-redshiftvarchar
tikirin tukurun
tikirin tukurun

Reputation: 141

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

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?

Upvotes: 1

Views: 4148

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269370

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

Upvotes: 2

Related Questions