Postgres timestamp with microseconds

Question

I have a column in a table with datatype set as timestamp without time zone.

I need the time part with microseconds(6 digits), but sometimes if the last digit is zero, the microseconds part ignores it.

I am able to query it with the below query to get 6 digits select to_char(now(), 'yyyy-mm-dd hh:mi:us'); 2020-07-16 12:05:598000

The above output is in text/char format but I need it in timestamp without time zone datatype with all 6 digits for microseconds even if one or some of the last digits are zero. The column datatype is timestamp without time zone

`to_char()` is perfectly find to display the value in a certain format. If you need a `timestamp` value, the display should not matter. — Erwin Brandstetter, Jul 16 '20 at 15:38

score 4 · Accepted Answer · answered Jul 16 '20 at 15:38

The number of significant digits does not come into play until you attempt to display the value. In this example, 46.392000 is equivalent to 46.392 just as it is with decimal numbers.

select '2020-07-16 11:35:46.392000-4'::timestamptz = '2020-07-16 11:35:46.392-4'::timestamptz;

 ?column? 
----------
 t

Where is it that you need to see all six digits with the dropped zeros? That is where you need to apply the formatting.

Postgres timestamp with microseconds

1 Answers1

Linked