String to timestamp in Postgresql

Question

Problem

How do you convert a string to a timestamp?

The documentation and all answers that I have found show how to convert at string column to timestamp with the to_timestamp function, but this apparently does not work for a single string.

What I have tried

to_timestamp('2019-09-20 13:59', 'DD-MM-YYYY HH24:MI:SS')

Cast('2019-09-20 13:59' as timestamp)

What I want to do

I want to add a column to a table with this date as a repeated value.

-- Creating timestamp column
ALTER TABLE my_table ADD creation_date timestamp

-- Repeating timestamp 
UPDATE my_table  SET creation_date = TO_TIMESTAMP('2019-09-20 13:59', 'YYYY-MM-DD HH24:MI')

What exactly is your problem? The code you have shown will work just fine: rextester.com/GQC96126 — user330315
– user330315, Commented Sep 23, 2019 at 8:22
The problem was that the code fails unless you write 'select' in front of it apparently. — Esben Eickhardt
– Esben Eickhardt, Commented Sep 23, 2019 at 8:25

Fahmi · Accepted Answer · 2019-09-23 08:19:38Z

7

You can try below - change your format mask from 'DD-MM-YYYY HH24:MI:SS' to 'YYYY-MM-DD HH24:MI:SS'

DEMO

select to_timestamp('2019-09-20 13:59', 'YYYY-MM-DD HH24:MI:SS')

answered Sep 23, 2019 at 8:19

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Esben Eickhardt Over a year ago

Oh, so the error message is because, you can not just run it without select?

mks-d Over a year ago

Thanks! I had a case where a date column was wrongly typed TEXT with ISO 8061 values, I did this: to_timestamp(date, 'YYYY-MM-DD"T"HH24:MI:SS').

Collectives™ on Stack Overflow

String to timestamp in Postgresql

Problem

What I have tried

What I want to do

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem

What I have tried

What I want to do

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related