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

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.

CodePudding user response：

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.

CodePudding user response：

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