How can we convert a given datetime format in PostgreSQL?

Question

I have a date time column which is of the following format:

2019-11-10-07.10.55.865000

I want my format to be as follows:

2019-11-10 07:10:55.865000

How can I do this in PostgreSQL 9.6.11?

Answer 1

We can try making a full roundtrip from text to timestamp, then back to text again:

SELECT
    TO_CHAR(TO_TIMESTAMP('2019-11-10-07.10.55.865000', 'YYYY-MM-DD-HH.MI.SS.US'),
        'YYYY-MM-DD HH:MI:SS.US') AS ts_out;

This outputs:

2019-11-10 07:10:00.865000

Demo

As a side note, you should seriously consider not storing your timestamps as text in the first place. Ideally, if you want to view your timestamp column a certain way, e.g. for reporting purposes, you should only have to make a single call to TO_CHAR with the format mask you want to use.

Answer 2

There is the to_char(timestamp, text) function, e.g. to_char(current_timestamp, 'HH12:MI:SS')

https://www.postgresql.org/docs/current/functions-formatting.html