Convert timestamp format in postgres

Question

I have a table as below

+----+----------------------------------+
| id |            Created_at            |
+----+----------------------------------+
|  1 | Monday 8 April 2019 8:30 AM      |
|  2 | Tuesday 9 April 2019 5:30 PM     |
|  3 | Wednesday 9 April 2019 8:30 PM   |
|  4 |  Thursday 10 April 2019 11:30 AM |
|  5 | Friday 11 April 2019 10:45 AM    |
+----+----------------------------------+

How do I get below table

+----+------------+
| id | Created_at |
+----+------------+
|  1 | 2019-04-08 |
|  2 | 2019-04-09 |
|  3 | 2019-04-09 |
|  4 | 2019-04-10 |
|  5 | 2019-04-11 |
+----+------------+

Basically I want to convert my Created_at column to YYYY-MM-DD format.

@sql_learner are you sure that every entry is in the right format of "Thursday 10 April 2019 11:30 AM" or is it possible to have also other random strings ? — Christos Karapapas
– Christos Karapapas, Commented Apr 14, 2019 at 10:20
If it’s always datetimes do use the correct data type and then possibilities for errors disappear and you can use all the date formatting tools available — Sami Kuhmonen
– Sami Kuhmonen, Commented Apr 14, 2019 at 10:42

D-Shih · Accepted Answer · 2019-04-14 10:39:28Z

2

From your sample data you can try to use ::DATE

Query #1

SELECT Created_at::DATE
FROM T;

| created_at               |
| ------------------------ |
| 2019-04-08T00:00:00.000Z |
| 2019-04-09T00:00:00.000Z |
| 2019-04-09T00:00:00.000Z |
| 2019-04-10T00:00:00.000Z |
| 2019-04-11T00:00:00.000Z |

View on DB Fiddle

answered Apr 14, 2019 at 10:39

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

1 Comment

sql_learner Over a year ago

Appreciate the help, Thanks !

Ancoron · Accepted Answer · 2019-04-14 10:39:43Z

1

PostgreSQL's date/time conversion is pretty flexible and should be able to deal with your format:

WITH
    data(Created_at) AS (VALUES
        ('Monday 8 April 2019 8:30 AM'),
        ('Tuesday 9 April 2019 5:30 PM'),
        ('Wednesday 9 April 2019 8:30 PM'),
        (' Thursday 10 April 2019 11:30 AM'),
        ('Friday 11 April 2019 10:45 AM ')
    )
SELECT trim(both from Created_at)::date AS Created_at
FROM data;

...results into:

 created_at 
------------
 2019-04-08
 2019-04-09
 2019-04-09
 2019-04-10
 2019-04-11
(5 rows)

answered Apr 14, 2019 at 10:39

Ancoron

2,7811 gold badge14 silver badges23 bronze badges

1 Comment

sql_learner Over a year ago

Appreciate the help, Thanks !

Collectives™ on Stack Overflow

Convert timestamp format in postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related