Alter the timestamp format in Postgres

Question

I've a createdAt field in a PostgreSQL table which is of the type timestamp with time zone. I want to change the format of it to include the milliseconds.

How do you suggest to change the timestamp format using the SQL query from 2018-08-21 10:35:28+00 to 2018-08-21 10:35:28.000+00?

Update:

Basically I want to convert second row createdAt to first row format with milliseconds.

Are you sure that the current timestamps do not include the millis? Or does your database viewer is just not showing them? What is the result of SELECT EXTRACT(MILLISECONDS FROM <your timestamp column>); This datatype should store the milliseconds. — S-Man
– S-Man, Commented Oct 5, 2018 at 10:08
I'm working with the Sails ORM which has recently changed the format they save for createdAt and I need to make the data consistant. On running the above query, I'm receiving 28000 as date_part which is showing milliseconds as 000 which is fine. I want to add that to the createdAt field. — SpaceX
– SpaceX, Commented Oct 5, 2018 at 10:18
Have you tried to use the to_char function (ie SELECT to_char(createdAt, 'YYYY-MM-DD HH:MI:SS.MS TZ') ) ? — Chocolord
– Chocolord, Commented Oct 5, 2018 at 10:34
But if you tried with the query and you got the 000 ms what is your problem? Did you inserted a new timestamp and the ms are truncated? If the old data do not have any ms parts they are stored with 000 ms. How would you like to add some ms parts? It seems that your second row does not have any ms part so your viewer just does not show ".000". But in the timestamp format the part exist internally. I am not sure if I understood your problem correctly. — S-Man
– S-Man, Commented Oct 5, 2018 at 10:53

Laurenz Albe · Accepted Answer · 2018-10-05 11:46:38Z

3

If you want your timestamp to be displayed in a specific format, you have to format it using the to_char function:

SELECT to_char(TIMESTAMPTZ '2018-08-21 10:35:28', 'YYYY-MM-DD HH24:MI:SS.MSOF');

          to_char           
----------------------------
 2018-08-21 10:35:28.000+00
(1 row)

answered Oct 5, 2018 at 11:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Alter the timestamp format in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related