Drizzle Team•16mo ago

where(gt) with timestamp returns wrong result

Might be a SQL inherent thing, but I'm a bit puzzled as to why this happens: Edit: for terseness I left out some code. I think the issue is clear

const firstThing = await db.insert(table).values({ timestamp: new Date() })
const secondThing = await db.insert(table).values({ timestamp: new Date() })

const result = await db.query.table.findFirst({ where: gt(table.timestamp, firstThing.timestamp) })
console.log(result) // logs firstThing, not secondThing

const firstThing = await db.insert(table).values({ timestamp: new Date() })
const secondThing = await db.insert(table).values({ timestamp: new Date() })

const result = await db.query.table.findFirst({ where: gt(table.timestamp, firstThing.timestamp) })
console.log(result) // logs firstThing, not secondThing

15 Replies

MartnartOP•16mo ago

Seeing that I couldn't find anything else in the discord regarding that, I have created an issue: https://github.com/drizzle-team/drizzle-orm/issues/1148

Angelelz•16mo ago

What are you trying to accomplish with gt function? firstThing is virtually going to be the same as secondThing Depending on your fsp value they might actually be the same I believe in in Mysql the default fsp is 0. So your precision will be 1 second. Give this a try:

const firstThing = await db.insert(table).values({ timestamp: new Date(Date.now() - 10000) })
const secondThing = await db.insert(table).values({ timestamp: new Date() })

const result = await db.query.table.findFirst({ where: gt(table.timestamp, firstThing.timestamp) })

const firstThing = await db.insert(table).values({ timestamp: new Date(Date.now() - 10000) })
const secondThing = await db.insert(table).values({ timestamp: new Date() })

const result = await db.query.table.findFirst({ where: gt(table.timestamp, firstThing.timestamp) })

Also, check what kind of precision your database is using to store the timestamp

MartnartOP•16mo ago

In pgAdmin I can see that the stored timestamp of secondThing is greater than the one of firstThing. I would expect this to be my ground truth in regards to sql queries. If I change the timestamp's mode to string it works. I'm assuming there's some precision lost in the conversion to a js Date?

Angelelz•16mo ago

That might be the case. The date in Js is equivalent to a millisecond precision or FSP=3

MartnartOP•16mo ago

But that still doesn't explain though, why a gt operator returns the same result it is comparing against. Note that the bug is not only that it doesn't return secondThing but that it does return firstThing the very value of which I was gting against

Angelelz•16mo ago

Can you enable logging in Drizzle to see what is that query producing? If firstThing (as a js value) has a lower precision than the actual value in the db. It might actually be lower Which would explain why you're getting that result

MartnartOP•16mo ago

Mode: string

Query: select "id", "customer_id", "descriptor", "status", "created_at", "started_at", "completed_at", "error" from "task" "tasks" where ("tasks"."descriptor" = $1 and "tasks"."customer_id" = $2 and "tasks"."created_at" > $3) limit $4 -- params: ["something", "c4185562-4d45-4e2a-a75d-9ba886f31838", "2023-08-30 18:35:58.540306", 1]

Query: select "id", "customer_id", "descriptor", "status", "created_at", "started_at", "completed_at", "error" from "task" "tasks" where ("tasks"."descriptor" = $1 and "tasks"."customer_id" = $2 and "tasks"."created_at" > $3) limit $4 -- params: ["something", "c4185562-4d45-4e2a-a75d-9ba886f31838", "2023-08-30 18:35:58.540306", 1]

Mode: Date

Query: select "id", "customer_id", "descriptor", "status", "created_at", "started_at", "completed_at", "error" from "task" "tasks" where ("tasks"."descriptor" = $1 and "tasks"."customer_id" = $2 and "tasks"."created_at" > $3) limit $4 -- params: ["something", "c4185562-4d45-4e2a-a75d-9ba886f31838", "2023-08-30T18:36:37.151Z", 1]

Query: select "id", "customer_id", "descriptor", "status", "created_at", "started_at", "completed_at", "error" from "task" "tasks" where ("tasks"."descriptor" = $1 and "tasks"."customer_id" = $2 and "tasks"."created_at" > $3) limit $4 -- params: ["something", "c4185562-4d45-4e2a-a75d-9ba886f31838", "2023-08-30T18:36:37.151Z", 1]

2023-08-30 18:35:58.540306 vs 2023-08-30T18:36:37.151Z

Angelelz•16mo ago

Yeah, there it is.. The problem is the precision in the Date Object I feel like the convenience of using native Date object outweighs the importance of having fsp=6

MartnartOP•16mo ago

But it totally breaks expected behavior in this case. It is the default mode after all

Angelelz•16mo ago

How do you think Drizzle should handle it? We can't force Date to have higher precision

MartnartOP•16mo ago

I am not sure how to properly handle it. At the very least I would expect a Warning box in the docs to say "if you need high precision, don't go for Date"

Angelelz•16mo ago

But it's up to the developer and the requirements of the app If you created an Issue in Github, would you be able to add what we've found and the suggestion to add a warning?

MartnartOP•16mo ago

Sure, I'll do that

Angelelz•16mo ago

Great, glad I could help 👍🏻

MartnartOP•16mo ago

Thank you

Gaming

Programming

where(gt) with timestamp returns wrong result