Postgres split timestamp column to time column and date column

Question

Hi as the title says I have a column named healthtime in my table which is of type timestamp without timezone i.e.

Healthime
2012-02-02 08:15:00

I would like to split this into two new columns in the same table, one date column and one time column i.e.

Date        Time
2012-02-02  08:15:00

Please can someone advise me how to do this (preferably in a single query),

Thanks, James

Answer 1

Not one query, but one transaction... should be fine as well:

My table looks like:

CREATE TABLE d (t timestamp);

Code for changing the table:

BEGIN;

ALTER TABLE d ADD COLUMN dat date;
ALTER TABLE d ADD COLUMN tim time;

UPDATE d
SET
    dat = t::date,
    tim = t::time;

ALTER TABLE d DROP COLUMN t;  

COMMIT;

Answer 2

SELECT Healthime::date from tbl_yourtable as date
SELECT Healthime::time(0) from tbl_yourtable as time

PostgreSQL Version: >= 10

Answer 3

select fake_table.healthtime
  , cast(to_char(fake_table.healthtime, 'YYYY-MM-DD') as date) as "Date"
  , cast(to_char(fake_table.healthtime, 'HH24:MI:SS') as time) as "Time"
from (select current_timestamp as "healthtime") as "fake_table"

output:

healthtime                  Date        Time
timestamp with time zone    date        time without time zone
==========================  ==========  ======================
2022-04-22 14:20:25.678-04  2022-04-22  14:20:25

tested on PostgreSQL 9.3.25, compiled by Visual C++ build 1600, 64-bit running on Windows 8.1