How to convert timestamp to datetime in postgres?

PostgrestimestampdatetimeTechnology
account_box
Algo Rhythmia
3 years ago

To convert a timestamp to datetime in PostgreSQL, you can use the TO_TIMESTAMP function. This function converts a string to a timestamp with timezone format.

The syntax for using the TO_TIMESTAMP function is as follows:

SELECT TO_TIMESTAMP(column_name, 'YYYY-MM-DD HH:MI:SS') AS new_column_name FROM table_name;

Here, column_name is the name of the timestamp column in the table, and new_column_name is the name of the new column that will store the converted datetime values.

The second argument to the TO_TIMESTAMP function is a string representing the format of the timestamp. In this example, we have used the format 'YYYY-MM-DD HH:MI:SS', where:

  • YYYY represents the year
  • MM represents the month
  • DD represents the day of the month
  • HH represents the hour
  • MI represents the minute
  • SS represents the second

So, if you have a timestamp column named timestamp_column in a table named my_table, and you want to convert it to datetime, you can use the following SQL query:

SELECT TO_TIMESTAMP(timestamp_column, 'YYYY-MM-DD HH:MI:SS') AS datetime_column FROM my_table;

This will create a new column named datetime_column in the my_table table, which will store the converted datetime values.