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?
2 Answers
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.
3 Comments
amith17
My output should be 2019-11-10 07:10:55.865000 instead of 2019-11-10 07:10:00.865000. 55 is missing according to this.
Tim Biegeleisen
@amith17 The demo code had a typo in it, the code in the answer was already correct.
amith17
If I convert it from text format to timestamp by using SELECT CAST(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 TIMESTAMP); the last three digits of 865000 are lost and I'm getting 2019-11-10 07:10:55.865 as output. Help me out.
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
timestampcolumns don't have "a format". Any format you see, is applied by the SQL client you are using to display the values.