Postgres Timestamp to DATE dd-mm-yyyy

Question

im trying to insert select a casted timestamp in a date colum timestamp like 02-09-2021 00:00:00, and i need to convert this timestamp to date dd-mm-yyyy without hhmmss, i've tried select date(column) as newdate but when i check my table insert, it keeps like timestamp, all the solutions that i tried only runs perfectly only in a select sentence, but when i try to insert select.. i keep with timestamp type..

A SELECT won't change the content or the data type of a column. If you want to change the column from timestamp to date your need to ALTER the table. — user330315
– user330315, Commented Jan 6, 2022 at 14:30
Change the data to a valid timestamp (the datatype that is) before you insert the data. — Frank Heikens
– Frank Heikens, Commented Jan 6, 2022 at 15:03

Stefanov.sm · Accepted Answer · 2022-01-06 15:35:00Z

4

If you need to convert formatted text to timestamp then use to_timestamp with explicit format specification. Try this:

select to_timestamp('02-09-2021 00:00:00', 'dd-mm-yyyy hh24:mi:ss');

to_timestamp
2021-02-09 00:00:00.000 +0200

To convert it to date:

select to_date('02-09-2021 00:00:00', 'dd-mm-yyyy');

If you need to change the type of an existing timestamp column to date then:

alter table the_table alter column the_column type date using the_column::date;

edited Jan 6, 2022 at 15:35

answered Jan 6, 2022 at 14:21

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

2 Comments

Raravar Over a year ago

i edited my question

Stefanov.sm Over a year ago

Will you post the DDL of your table(s) please? Where does 02-09-2021 00:00:00 come from, another table? It looks like formatted text. Please note that dates and timestamps do not have format. They can only be presented in different formats.

Collectives™ on Stack Overflow

Postgres Timestamp to DATE dd-mm-yyyy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related