im trying to insert in a table a column that is timestamp like 02-09-2021 00:00:00, and i need to convert this timestamp to date dd-mm-yyyy, 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..

CodePudding user response：

You need to_timestamp with explicit format specification. Try this:

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

CodePudding user response：

If you're inserting in the table as a string, you can create a substring from the value since you only want to store the date of the timestamp.

For this you can do,

SELECT timestamp,
substring(timestamp, 1, 10) // <- Indexes start at 1 and dd-mm-yyyy are 10 in total)
from timestamp_column;

Alternatively, you can create a function and by suffixing the method with ::timestamp::date you can extract just the date part.

As an example,

select '02-09-2021 00:00:00'::timestamp::date;

Returns 2021-02-09. From this you can just convert to the desired format.