Convert VARCHAR to TIMESTAMP (AWS REDSHIFT/POSTGRESQL)

Question

I have the following problem, at source I have the data coming as varchar

example of data source in varchar format:

• 08:15:49
• 18:16:05
• 20:01:33

etc....

I need to reinsert this data into a new table so I did an Insert with the following select

INSERT INTO NOVA_TABELA(
    NOVO_CAMPO
)
SELECT  
    TO_TIMESTAMP(hora, 'HH24:MI:SS') 
FROM TABELA_ANTIGA;

The problem is that I only need the time, and the timestamp is adding date next to the time

output from processing:

• 0001-01-01 08:15:49
• 0001-01-01 18:16:05
• 0001-01-01 20:01:33

I want you to just convert the source string to time without adding anything, but I’m not getting it...

Answer 1

The conversion function TO_TIMESTAMP() returns the type TIMESTAMP. You need to use a CAST for the guy TIME in order to convert the data, see only:

TO_TIMESTAMP( hora, 'HH24:MI:SS' )::TIME

In your case:

INSERT INTO NOVA_TABELA(
    NOVO_CAMPO
)
SELECT  
    TO_TIMESTAMP(hora, 'HH24:MI:SS')::TIME 
FROM TABELA_ANTIGA;

Let’s take a practical example, assuming your source table is something like:

CREATE TABLE tb_origem
(
  id BIGINT PRIMARY KEY,
  hora VARCHAR(8)
);

INSERT INTO tb_origem ( id, hora ) VALUES ( 1, '08:15:49' );
INSERT INTO tb_origem ( id, hora ) VALUES ( 2, '18:16:05' );
INSERT INTO tb_origem ( id, hora ) VALUES ( 3, '20:01:33' );

And your target table:

CREATE TABLE tb_destino
(
  id BIGINT PRIMARY KEY,
  hora TIME
);

You can convert a VARCHAR for TIME as follows:

INSERT INTO tb_destino (id, hora )
(SELECT id, to_timestamp( hora, 'HH24:MI:SS' )::time FROM tb_origem);

See working on Sqlfiddle