Transform Timestamp Without Time Zone to a simple date in PostgreSQL

Question

Suppose I have a column that has the datatype Timestamp without Time Zone and looks like this:

Sun Jan 01 1933 00:00:00 GMT+0000 (Coordinated Universal Time)

I want to change it to a simple date like this 01/01/1933. I have tried the following, but there is no change in the output:

SELECT timestamp_date::date as new_date,
       DATE(timestamp_date) as also_date
FROM my_schema.my_table;

What am I doing wrong here?

Your date is already a date, in ISO format. If you want to transform this to some different format, text, you can you the function to_char() and the parameters you need. Personally I would never do this in the database, but always in the presentation layer of my application — Frank Heikens
– Frank Heikens, Commented Jul 29, 2022 at 17:08
Can you fiddle it? Also remember you can cast as ::timestamp without time zone — user_0
– user_0, Commented Jul 29, 2022 at 17:10
@FrankHeikens, no that is not a date format it is datetime format. Nor is it the ISO format. A ISO date format would be 1993-01-1 See here ISO 8601 for more information. — Adrian Klaver
– Adrian Klaver, Commented Jul 29, 2022 at 17:51
What version of Postgres? What client are you using to run the query? Because this Sun Jan 01 1933 00:00:00 GMT+0000 (Coordinated Universal Time) is not valid in Postgres: select 'Sun Jan 01 1933 00:00:00 GMT+0000 (Coordinated Universal Time)'::timestamp; ERROR: invalid input syntax for type timestamp: "Sun Jan 01 1933 00:00:00 GMT+0000 (Coordinated Universal Time)" — Adrian Klaver
– Adrian Klaver, Commented Jul 29, 2022 at 17:59
@AdrianKlaver: missed that completely! Just saw the casting to a date… Need some coffee, too early in the morning to start without — Frank Heikens
– Frank Heikens, Commented Jul 29, 2022 at 18:03

Anthony Sotolongo · Accepted Answer · 2022-07-29 17:12:31Z

3

you can use the function to_char

to_char(timestamp_date, 'DD/MM/YYYY');

Or you can play with DATESTYLE configuration parameters, but use to_char always as possible, to avoid the change of configuration

answered Jul 29, 2022 at 17:12

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

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

1 Answer 1