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
– Erwin Brandstetter, Commented Jul 16, 2020 at 15:38

Mike Organek · Accepted Answer · 2020-07-16 15:38:10Z

5

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.

answered Jul 16, 2020 at 15:38

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres timestamp with microseconds

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related