Drizzle Team•14mo ago

Issue filtering by date equality

I'm running into an issue where I don't get any results for a value with matches in the db. Example query using date string directly from db gives me two results:

SELECT id, starting_at
FROM public.meeting_events
WHERE meeting_events.starting_at = '2023-11-01 23:00:40.145'
ORDER BY meeting_events.starting_at, id ASC
LIMIT 30;

SELECT id, starting_at
FROM public.meeting_events
WHERE meeting_events.starting_at = '2023-11-01 23:00:40.145'
ORDER BY meeting_events.starting_at, id ASC
LIMIT 30;

If I send this value through new Date('xxx').toISOString() I get back: 2023-11-02T03:00:40.145Z Same query with this value returns 0 results

SELECT id, starting_at
FROM public.meeting_events
WHERE meeting_events.starting_at = '2023-11-02T03:00:40.145Z'
ORDER BY meeting_events.starting_at, id ASC
LIMIT 30;

SELECT id, starting_at
FROM public.meeting_events
WHERE meeting_events.starting_at = '2023-11-02T03:00:40.145Z'
ORDER BY meeting_events.starting_at, id ASC
LIMIT 30;

I'm seeing the same behavior when trying to equality filter this value using drizzle IE: where: eq(meetingEvents.startingAt, decodedCursor ? new Date(decodedCursor.startingAt) : afterDate), Not sure if this is a known issue people have run into or if I'm just doing something wrong. Like maybe I need to use dates in string mode?

8 Replies

Angelelz•14mo ago

Dates are always a problem. You can use new Date(...).toISOString().replace("T", " ").replace("Z", "") to get the format used in SQL Actually, you need to account for your timezone too. lol

danecandoOP•14mo ago

Aren't they though 🥹 Thanks for the response. Looking forward to chewing on this and figuring out how to make sure date strings are correctly formatted throughout the system so my queries dont break sadface

Angelelz•14mo ago

I have date helpers in all my projects. It's a pain

danecandoOP•14mo ago

@Angelelz after switching to string mode it looks like the driver or orm still delivers dates in iso8601 format - is that your experience as well? Off the top of my head the only time that would require format conversion on the client is for comparison in where?

Angelelz•14mo ago

What dialect are you using? pg, mysql or sqlite

danecandoOP•14mo ago

ah right postgres.js

Angelelz•14mo ago

Take a look at this issue, might be what you're facing

Angelelz•14mo ago

https://github.com/drizzle-team/drizzle-orm/issues/877

GitHub

[BUG]: Postgres truncate milliseconds from timestamp · Issue #877 ·...

What version of drizzle-orm are you using? 0.27.0 What version of drizzle-kit are you using? 0.19.3 Describe the Bug The postgres Timestamp column's mapFromDriverValue method was incorrectly tr...

Gaming

Programming

Issue filtering by date equality