In my postgres database I have a table I’ll call users with a timestamptz type column updated_time. When I execute a select statement on this field I will get something like this – ‘2024-07-01 12:30:30.821 -0500’. However, if I turn around and select from this table using this value such as

select * from users where updated_time = '2024-07-01 12:30:30.821 -0500'

I get no results. This is due to a hidden precision. If I play around with it I can trial/error the milliseconds to construct a query with a millisecond precision of 6 decimal places which will return results such as:

select * from users where updated_time = '2024-07-01 12:30:30.821898 -0500'

My question is, does anyone know why this could be happening? The table column seems to have a 6 millisecond precision that the select statement does not reflect.

Additional note: I did try running the query

select * '2024-07-01 12:30:30.821898 -0500'::timestamptz

Which gives me the millisecond truncated value: ‘2024-07-01 12:30:30.821 -0500’

I then tried

select '2024-07-01 12:30:30.821898 -0500'::timestamptz(6);

And this still gives me the truncated value: ‘2024-07-01 12:30:30.821 -0500 -0500’

It seems like no matter what I do I’m stuck with a precision of 3 millisecond decimal places when selecting.

Any help would be much appreciated. Thanks!