'How do I extract a date (dd-mm-yy) from a timestamp with timezone (timestamptz) in postgresql

My date column "timestamp" is currently listed as:

2020-11-16 20:27:38.033 +0000

It's formatted as timestamptz and I've tried every search on here and google to find a method to only pull the date part (in this example 2020-11-16) from the column so I can effectively start grouping data by Date.

Any help would be greatly appreciated.

sqlpostgresqltimestamp-with-timezone


Solution 1:[1]

Assuming (as you haven't stated) that the column is a string. This shows how to convert:

postgres=# SELECT ('2020-11-16 20:27:38.033 +0000'::timestamp)::date;
    date    
------------
 2020-11-16

If it were already a timestamp, then just the ::date cast would work.

Solution 2:[2]

You can use ::DATE casting or use TO_CHAR() conversion if the aim is just to display in that format such as

SELECT your_ts_column::DATE AS val_as_date,
       TO_CHAR(your_ts_column, 'YYYY-MM-DD') AS val_as_str 
  FROM your_table

Demo

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 w08r
Solution 2

Related Questions