Postgres truncate the millisecond part from three to two digits

user_5944884 0
Viewed 390

I am formatting an input that I get in Timestamp datatype and sending the output as a text in the below format

2020-07-30 10:45:23.638 PM

For this I'm using the below query

select to_char(input_timestamp, 'YYYY-MM-DD HH:MI:SS.MS AM');

But I want the microsecond part truncated to 2 digits instead of 3 digits. The output will be in TEXT datatype. Expecting help on this.

postgresql
1 Answers

Try this:

select 
    left(to_char(input_timestamp::timestamp(2), 'YYYY-MM-DD HH:MI:SS.MS'), 22) || 
    to_char(input_timestamp, 'FM AM');

First round to two microsecond' digits, convert to string, chop to 22 characters and then concatenate the meridiem indicator.

user_23020320
Related