postgresqldatetimepostgresql-9.6format-conversion

How can we convert a given datetime format in PostgreSQL?

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?

Solution

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