Convert VARCHAR to TIMESTAMP (AWS REDSHIFT / POSTGRESQL)

Question

Convert VARCHAR to TIMESTAMP (AWS REDSHIFT / POSTGRESQL)

Navigation

#1 by (1 votes)

0

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

Sample source data in varchar format:

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

etc ...

I need to re-insert this data into a new table so I made 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

transformation output:

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

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

sql postgresql aws

asked by anonymous 04.07.2018 / 23:26

1 answer

How to create a local network (intranet) with django? [closed] Increase Spirograph loop speed in turtle

score 1 · Answer 1

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

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 VARCHAR to TIME as follows:

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

See working in SQLFiddle