Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

from_now/datetime_add lose precision on _usec family of types #3067

Closed
sumerman opened this issue Jul 22, 2019 · 0 comments

Comments

@sumerman
Copy link
Contributor

commented Jul 22, 2019

Environment

  • Elixir version (elixir -v):
Erlang/OTP 22 [erts-10.4.4] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [hipe] [dtrace]
Elixir 1.9.0 (compiled with Erlang/OTP 22)
  • Database and version: PostgreSQL 10.5
  • Ecto version (mix deps): ecto 3.1.7 (Hex package) (mix) locked at 3.1.7 (ecto) fa21d06e ecto_sql 3.1.6 (Hex package) (mix) locked at 3.1.6 (ecto_sql) 1e80e30d
  • Database adapter and version (mix deps): postgrex 0.15.0 (Hex package) (mix) locked at 0.15.0 (postgrex) dd534916
  • Operating system: OS X 10.14.5

Current behavior

This issue is a follow up for this thread

I’m trying to update a project to use Ecto 3.1. One of the models have the following field defined:

# used to be just :naive_datetime
field :last_updated,  :naive_datetime_usec, default: @default_timestamp

Previously I used it in the following query:

      from e in MyModel,
        join: se in assoc(e, :segment_export),
        where: e.last_updated < from_now(^minus_n, ^tu),
        order_by: e.last_updated,
        preload: [segment_export: se]

But now it returns an empty result set, because, in tests, I have very tight time boundaries.
Inspecting the SQL Ecto generates I can see that the problem is caused by DateTime.utc_now being encoded with reduced precision:

SELECT <redacted> FROM "foo_exports" AS f0 INNER JOIN "segment_exports" AS s1 ON s1."id" = f0."segment_export_id" WHERE (f0."last_updated" < $1::timestamp + ($2::numeric * interval '1 millisecond')) ORDER BY f0."last_updated" LIMIT 1 FOR UPDATE SKIP LOCKED [~N[2019-07-19 20:10:03], #Decimal<-1>]

I managed to get the precision I want by altering the query

      from e in MyModel,
        join: se in assoc(e, :segment_export),
        where: e.last_updated < datetime_add(type(^DateTime.utc_now, :naive_datetime_usec), ^minus_n, ^tu),
        order_by: e.last_updated,
        preload: [segment_export: se]

With the typecast in place, the timestamp obtained from utc_now is represented as ~N[2019-07-20 09:29:59.044696] and therefore have enough precision for the query to see a row inserted earlier in the test every time.

Expected behavior

The original query

from e in MyModel,
        join: se in assoc(e, :segment_export),
        where: e.last_updated < from_now(^minus_n, ^tu),
        order_by: e.last_updated,
        preload: [segment_export: se]

works as it used to.

@michalmuskala michalmuskala self-assigned this Jul 22, 2019

@josevalim josevalim closed this in b1c8d38 Jul 24, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.