AE
Ash Elixir•3y ago
mylanconnolly

Issue using datetime_add

Hello, thanks again for the awesome library, I was trying to get into some more useful functionalities of Ash (calculations, etc.) and I'm mostly having a good time learning about it but there was one bit that I was having a hard time with. I was modeling an appointment, which I wanted to store the start date and the duration of. I thought calculating the end time would be useful, so I added made the following resource: 
use Ash.Resource, data_layer: AshPostgres.DataLayer

# ...

calculations do
  calculate(
    :end_time,
    :utc_datetime,
    expr(datetime_add(start_time, duration, :minute))
  )
end

attributes do
  uuid_primary_key(:id)

  attribute :start_time, :utc_datetime do
    allow_nil?(false)
  end

  attribute :duration, :integer do
    default(30)
  end

  attribute(:description, :string)

  timestamps()
end
use Ash.Resource, data_layer: AshPostgres.DataLayer

# ...

calculations do
  calculate(
    :end_time,
    :utc_datetime,
    expr(datetime_add(start_time, duration, :minute))
  )
end

attributes do
  uuid_primary_key(:id)

  attribute :start_time, :utc_datetime do
    allow_nil?(false)
  end

  attribute :duration, :integer do
    default(30)
  end

  attribute(:description, :string)

  timestamps()
end
If I attempt to load the calculation, I get an error: 
Mindset.Patients.Appointment.read_all(load: :end_time)
** (Postgrex.Error) ERROR 42846 (cannot_coerce) cannot cast type interval to timestamp without time zone
Mindset.Patients.Appointment.read_all(load: :end_time)
** (Postgrex.Error) ERROR 42846 (cannot_coerce) cannot cast type interval to timestamp without time zone
This is the SQL that is generated: 
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", a0."start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute')::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", a0."start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute')::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
If I tweak it to include parentheses around the timestamp calculation, it works: 
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", a0.("start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute'))::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", a0.("start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute'))::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
Is there something I'm doing incorrectly in defining the calculation? Thanks!
3 Replies
ZachDaniel
ZachDaniel•3y ago
Hm…this might be a bug in ash postgres 🙂 I'll take a look in a few hours when I'm home Can you try ash_postgres main branch?
mylanconnolly
mylanconnollyOP•3y ago
Just had a chance to check it out and it generated SQL that Postgres likes: 
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", (a0."start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute'))::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
SELECT a0."id", a0."start_time", a0."duration", a0."description", a0."inserted_at", a0."updated_at", a0."patient_id", (a0."start_time"::timestamp + (a0."duration"::bigint::numeric * interval '1 minute'))::timestamp FROM "client_0d98e96c-ddb3-43e2-98ee-df027173c950"."appointments" AS a0
Thanks so much for checking into it!
ZachDaniel
ZachDaniel•3y ago
👍 my pleasure, thanks for the report!

Did you find this page helpful?