CODEWITHSUNDEEP
sqlpostgresql
sql_learner
sql_learner

Reputation: 547

Convert timestamp format in postgres

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.

Upvotes: 0

Views: 433

Answers (2)

Ancoron
Ancoron

Reputation: 2733

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)

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 2

Related Questions