DateTime format in PostgreSQL?

Question

Can anyone tell please tell me how to change 2018-01-15T08:54:45.000Z to 2018-01-15 08:54:45 in PostgreSQL.

Here my timestamp: 2018-01-15T08:54:45.000Z is in text format. I need to split it into two different columns like one for the only date:2018-01-15 and another is for only time:08:54:45

Andrew Whitaker · Accepted Answer · 2019-03-01 19:21:45Z

6

You should be able to directly cast your text to a timestamp and then cast again to date or time to get two different columns:

SELECT
  ('2018-01-15T08:54:45.000Z'::timestamp)::time AS time,
  ('2018-01-15T08:54:45.000Z'::timestamp)::date AS date
;

answered Mar 1, 2019 at 19:21

Andrew Whitaker

126k32 gold badges295 silver badges308 bronze badges

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

1 Comment

Dark Star Over a year ago

Hello @Andrew Whitaker, many thanks for your answer. This code converting the timestamp properly but it shows some extra digit after second as example 15:30:59.00163. In my case its work like UPDATE table_name SET time = ('timestamp'::TIMESTAMP)::timestamp(0);

Collectives™ on Stack Overflow

DateTime format in PostgreSQL?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related