Date display as DATE HH:MI:SS in postgres-CodePudding

I have a date_timestamp column in the database that stores UTC time. Example:

2022-01-09 15:38:47.487-05
2022-01-08 21:28:30.989492-05

But in reports, I want to show only DATE and HH:MI:SS. I would like to show it as DATE and not Character string.

eg:

2022-01-09 15:38:47
2022-01-08 21:28:30

Doing a character conversion, like select to_char(timestampcreated,'YYYY-MM-DD HH:MI:SS') cc shows the data the way I want but it's in CHARACTER not a date.

Any data type conversion on the "character" data is messed up. Either I can get date, or time but not both.

How can I achieve this?

CodePudding user response：

If you really want just a date:

select '2022-01-09 15:38:47.487-05'::timestamptz::date; 

2022-01-09

select pg_typeof('2022-01-09 15:38:47.487-05'::timestamptz::date); 

date

Though what you show as desired result is a timestamp, so:

select date_trunc('second', '2022-01-09 15:38:47.487-05'::timestamp);

2022-01-09 15:38:47

select pg_typeof(date_trunc('second', '2022-01-09 15:38:47.487-05'::timestamp));
         
timestamp without time zone

Though as @a_horse_with_no_name says I'm not sure why it matters for a report output whether it is a character or date/time type.

For more information on date_trunc see date_trunc