Open jwoertink opened 2 years ago
The reason is that timestamp types in posgresql only store the time value with microsecond precision but Crystal's Time
type has nanosecond precision.
at_end_of_day
results in a time instance with 999_999_999 nanoseconds. When postgres parses that time information, it rounds the excess precision to the next full microsecond, which is the first microsecond of the next day.
You can observe the same behaviour running this SQL query:
SELECT '2022-06-13T23:59:59.999999999999Z' :: timestamp;
-- timestamp
-- ---------------------
-- 2022-06-14 00:00:00
-- (1 row)
Oh lovely. Good catch. I guess we need Time.utc.almost_at_the_end_of_the_day
:joy:
Or Time.utc.at_the_end_of_the_day.at_beginning_of_microsecond
🤷
If that is the case, can this line be updated to 6
to only do the microseconds?
https://github.com/will/crystal-pg/blob/master/src/pq/param.cr#L23
Running a quick test on my end makes the example work.
Yes, that should fix this problem. There are other implications, though. Truncating the fraction digits results in an odd rounding behaviour that might be unexpected at some times. So I'm not sure if the driver should handle this. Would be interesting to look how other drivers handle excess precision.
It seems when passing in a Time object set to
at_end_of_day
as an arg, it somehow loses the precision and rolls in to the beginning of the next day.Here's an example where I generate a date series. The first SQL has the time set manually, and when ran, it returns 1 result properly. The second SQL pulls the times from Crystal and passes it over as as args. Even though the Crystal times have the same values, running this SQL returns 2 results instead of 1.