Timestamptz(Usec) is cast to `timestamp` in when performing calculation, leading to erroneous queries

[Munksgaard]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

* ash 3.9.0 (Hex package) (mix)
  locked at 3.9.0 (ash) e3e80a18
* ash_postgres 2.6.25 (Hex package) (mix)
  locked at 2.6.25 (ash_postgres) dadf95df
* ash_sql 0.3.12 (Hex package) (mix)
  locked at 0.3.12 (ash_sql) 2d1e8bff

Operating system

NixOS

Current Behavior

I have the following attributes and calculations:

  attributes do
    integer_primary_key :id

    attribute :bar, AshPostgres.Timestamptz, allow_nil?: false
  end

  calculations do
    calculate :past_bar?, :boolean, expr(bar <= now())
  end

I then have this test:

  alias AshTimestamptzError.Foos.Foo

  test "recently created foo should have true `past_bar?`" do
    foo =
      Ash.create!(Foo, %{bar: DateTime.utc_now()})

    assert Ash.get!(Foo, foo.id, load: :past_bar?).past_bar?
  end

When running this on a machine that has the clock set to UTC+1, this test fails, because the query to load past_bar? looks like this:

14:15:37.018 [debug] QUERY OK source="foos" db=4.9ms queue=0.8ms
SELECT f0."id", f0."bar", (f0."bar"::timestamp <= $1::timestamp::timestamp)::boolean FROM "foos" AS f0 WHERE (f0."id"::bigint = $2::bigint) LIMIT $3 [~U[2025-11-14 13:15:37.007177Z], 5, 2]

Note that bar and the input timestamp ($1) are being cast to timestamp, throwing away time zone information. Instead, they should've been cast to timestamptz, and indeed that's what I thought would happen, since I am using AshPostgres.Timestamptz as the type.

Reproduction

I have a created a replication repo here: Munksgaard/ash_timestamptz_error@d51a0ca.

However, do note that you probably need to be using a UTC+ timezone, e.g. it won't work on a computer/postgres instance set to run with US timezone.

Expected Behavior

The database query should detect that bar is a AshPostgres.Timestamptz and cast it (and the input timestamp) to timestamptz.

[zachdaniel]

🤔 this is a tough one. I think what we need to do is add operator overrides for the comparison functions, similar to what we do with AshMoney. https://github.com/ash-project/ash_money/blob/v0.2.5/lib/ash_money/types/money.ex#L152

PR welcome for this approach!

[Munksgaard]

I could probably try to add such an override, but I'd really like to understand why this breaks in the first place. I've been looking at Expr.determine_types/4, which is called like this:

bar = %{
    attribute: %{
      default: nil,
      name: :bar,
      type: AshPostgres.Timestamptz,
      description: nil,
      source: :bar,
      __struct__: Ash.Resource.Attribute,
      constraints: [
        precision: :second,
        cast_dates_as: :start_of_day,
        timezone: :utc
      ],
      allow_nil?: false,
      public?: false,
      __spark_metadata__: %{
        anno: [
          file: ~c"/home/munksgaard/tmp/ash_timestamptz_error/lib/ash_timestamptz_error/foos/foo.ex",
          location: 20
        ],
        __struct__: Spark.Dsl.Entity.Meta,
        properties_anno: %{}
      },
      match_other_defaults?: false,
      sensitive?: false,
      primary_key?: false,
      update_default: nil,
      filterable?: true,
      sortable?: true,
      writable?: true,
      select_by_default?: true,
      generated?: false,
      always_select?: false
    },
    resource: AshTimestamptzError.Foos.Foo,
    __struct__: Ash.Query.Ref,
    relationship_path: [],
    combinations?: false,
    simple_equality?: nil,
    input?: false,
    bare?: nil
  }

now = %{
    extra: %{},
    name: :now,
    arguments: [],
    __struct__: Ash.Query.Function.Now,
    __predicate__?: false,
    embedded?: false,
    __function__?: true
  }

Ash.Expr.determine_types(Ash.Query.Operator.LessThanOrEqual,
  [bar, now], {Ash.Type.Boolean, []}, false)

When called like that it returns:

{[{Ash.Type.UtcDatetimeUsec, []}, {Ash.Type.UtcDatetimeUsec, []}],
 {Ash.Type.Boolean, []}}

I would expect it to return:

{[{AshPostgres.Timestamptz,
  [precision: :second, cast_dates_as: :start_of_day, timezone: :utc], 
 {AshPostgres.Timestamptz,
  [precision: :second, cast_dates_as: :start_of_day, timezone: :utc]],
 {Ash.Type.Boolean, []}}

Calling Ash.Expr.determine_type/1 directly on bar returns:

{:ok,
 {AshPostgres.Timestamptz,
  [precision: :second, cast_dates_as: :start_of_day, timezone: :utc]}}

Whereas calling Ash.Expr.determine_type/1 directly on now() returns:

{:ok, {Ash.Type.UtcDatetimeUsec, []}}

determine_types/4 calls determine_type/1 directly and gets the right type out for bar, but somewhere inside determine_types/4 it is throwing that information away, possibly because it is being unified with the type from now(), but I can't really figure out where.

[Munksgaard]

Aha, changing the result of Ash.Query.Function.Now.returns/0 to return AshPostgres.TimestamptzUsec makes determine_types/4 return AshPostgres.TimestamptzUsec and the query then correctly uses timestamptz. This is of course way too invasive to change, but I'm wondering if there's a way to fix the unification (or whatever it is that determine_types/4 does) to take the type from bar instead of now().

[Munksgaard]

I'm also wondering how related this all is to #264

[Munksgaard]

I've just updated my reproduction repo: Munksgaard/ash_timestamptz_error@b338abd

It now uses usec precision, and I've added another calculated field: past_bar2?, which does the exact same calculation, just flipping the inequality around (bar <= now() => now() > bar), and which works!

The query executed when calculating past_bar2? looks like this:

SELECT f0."id", f0."bar", ($1::timestamptz::timestamptz >
f0."bar"::timestamptz)::boolean FROM "foos" AS f0 WHERE (f0."id"::bigint =
$2::bigint) LIMIT $3 [~U[2025-11-15 10:06:17Z], 34, 2]

I think the difference is that Ash.Expr.determine_types/4 is sensitive to the
order of the arguments, since it applies a "fallback" type, and whichever value
is last gets to override the fallback type of the other arguments. Specifically, this happens on line 1212. Should it override the fallback basis if one is already set? Should it depend on the order of the arguments? Should they be unified in some other way?

The usec precision is required to avoid false test failures (both positive and
negative), because the time doesn't change enough in between creating a foo
and getting past_bar?/past_bar2?.

[zachdaniel]

Interesting 🤔 I do think that adding override types would specifically solve this though, essentially informing us that we can directly compare these two types without casting for example. It's worth examining that before diving into the type casting logic I think.

[Munksgaard]

I'm not sure I'm using operator_overloads correctly, but I tried to add some here: Munksgaard@16fced4

Unfortunately, it didn't work. Here's the query I get for the test in the reproduction repo above:

22:39:11.059 [debug] QUERY OK source="foos" db=13.0ms queue=1.1ms
SELECT f0."id", f0."bar", (f0."bar"::timestamptz(6) <= $1::timestamp::timestamp)::boolean 
FROM "foos" AS f0 WHERE (f0."id"::bigint = $2::bigint) LIMIT $3 [~U[2025-11-15 21:39:11.032727Z], 45, 2]

Notice that bar is being cast into timestamptz, but now is being left as is. But now should also be a timestamptz for the calculation to be correct.

[Munksgaard]

I am no PostgreSQL expert, but I also think it might be wrong, in general, to cast the input timestamp to timestamp. The input timestamp is given at UTC timezone, e.g. 2025-11-15 22:22:45.530140Z (notice the Z at the end), but if I have my timezone set to Europe/Copenhagen, it is being interpreted as 2025-11-15 22:22:45.530140+01 which is equivalent to 2025-11-15 21:22:45.530140Z. In other words, the now in my code suddenly becomes a point in the past. And this behavior changes when the timezone of the database changes, e.g. if you run set timezone = 'America/Los_Angeles';

[zachdaniel]

Yeah, I don't think it's really communicate, but right now I think things are assuming that the database is in UTC 🤔. We can possibly make the default logic smarter by casting as a timestamp with time zone? I'd have to brush up on my "how does PostgreSQL do timestamps", but that would also be an option? We'd just have to make sure whatever we do is backwards compatible. Or gated behind a setting.

[Munksgaard]

We can possibly make the default logic smarter by casting as a timestamp with time zone?

At least then we wouldn't be throwing away the timezone information, but I'm having a hard time wrapping my head around what that would mean in general.

However, I'm wondering if we need that casting at all? It doesn't seem like Ecto does it. Here's a similar Ecto query and the resulting SQL query:

q = from f in Foos.Foo, where: f.id == ^id, select: f.bar <= ^DateTime.utc_now()
Repo.one(q)

22:45:58.616 [debug] QUERY OK source="insurance_claims" db=0.5ms queue=0.7ms idle=1429.8ms
SELECT i0."bar" <= $1 FROM "foos" AS i0 WHERE (i0."id" = $2) [~U[2024-05-26 16:14:01.000000Z], 1]

[Munksgaard]

Alternatively, if we implement #264, we could just make all those casts into timestamptz when the option is set? My understanding as a layperson is that timestamptz should ideally be used everywhere, except when handling NaiveDateTime.