Timestamp select/parsing precision issue with Postgres
Hi,
I have a postgres timestamp column on my database, using drizzle-orm v0.30.1. When I write dates to the column, I see in my database that they are being written with a precision of 6 digits of milisecond precision, e.g. 2024-03-03 08:25:18.380572+00. However, when I select that column into my data, it seems to only ever parse the value with 3 miliseconds of precision, e.g. 2024-03-03T08:25:18.380Z. I don't have any additional parameters specified on the column, it's simply defined as
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
I see in the docs the addition of the 'precision' parameter to the timestamp, but am confused about why I am getting different behavior in writes vs reads if I'm just using the defaults in the column for both reads and writes (perhaps related to the default value defined in postgres?)
I couldn't find any information about default precision in the docs here, and am wondering whether there is some mismatch in default precision values between writing and reading, e.g. if the drizzle default for reading is 3, but the postgres default for writing is 5? Is the only way to force these to be the same to define a precision value on all my columns?
https://orm.drizzle.team/docs/column-types/pg#timestamp
The reason this becomes troublesome is that I am trying to use a datetime as a cursor value in pagination, but due to the loss of precision I am getting incorrect paging results by truncating the precision of the cursor when I pass it back into my query to fetch the 'next page'.
Thank you!
I have a postgres timestamp column on my database, using drizzle-orm v0.30.1. When I write dates to the column, I see in my database that they are being written with a precision of 6 digits of milisecond precision, e.g. 2024-03-03 08:25:18.380572+00. However, when I select that column into my data, it seems to only ever parse the value with 3 miliseconds of precision, e.g. 2024-03-03T08:25:18.380Z. I don't have any additional parameters specified on the column, it's simply defined as
createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow()
I see in the docs the addition of the 'precision' parameter to the timestamp, but am confused about why I am getting different behavior in writes vs reads if I'm just using the defaults in the column for both reads and writes (perhaps related to the default value defined in postgres?)
I couldn't find any information about default precision in the docs here, and am wondering whether there is some mismatch in default precision values between writing and reading, e.g. if the drizzle default for reading is 3, but the postgres default for writing is 5? Is the only way to force these to be the same to define a precision value on all my columns?
https://orm.drizzle.team/docs/column-types/pg#timestamp
The reason this becomes troublesome is that I am trying to use a datetime as a cursor value in pagination, but due to the loss of precision I am getting incorrect paging results by truncating the precision of the cursor when I pass it back into my query to fetch the 'next page'.
Thank you!
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
