Ecto fragment macro supplying a time zone?

Questions / Help
,
1

I am writing something that has to aggregate based on time related functions with time zones. I wrote this macro, one of many:

defmacro hour_fragment(field, time_zone) do
  quote do
    fragment(
      "extract(hour from ? at time zone 'utc' at time zone ?)::integer",
      unquote(field),
      ^unquote(time_zone)
    )
  end
end

Which totally works.

tz = "America/New_York"
query() |> select([mytable: t], t.interval |> hour_fragment(tz)) |> Repo.all()

Until I do a group by

tz = "America/New_York"
query() |> group_by([mytable: t], t.interval |> hour_fragment(tz)) |> select([mytable: t], t.interval |> hour_fragment(tz)) |> Repo.all()

** (Postgrex.Error) ERROR 42803 (grouping_error) column "i0.interval" must appear in the GROUP BY clause or be used in an aggregate function

query: SELECT extract(hour from i0."interval" at time zone 'utc' at time zone $1)::integer FROM "interval_stats" AS i0 GROUP BY extract(hour from i0."interval" at time zone 'utc' at time zone $2)::integer

If I actually put that query into postgresql, and exchange $1 and $2 for the same string, it works.

Furthermore if I change the macro to hard code in the time zone, that works too.

defmacro hour_fragment(field, time_zone) do
  quote do
    fragment(
      "extract(hour from ? at time zone 'utc' at time zone 'America/New_York')::integer",
      unquote(field)
    )
  end
end

I feel like there must be something simple I’m missing. I also tried to just interpolate that string into the query because it is trustworthy, but I can’t do that either.

2

:wave:

Just as a test, could you please try

tz = "America/New_York"
query()
|> select([mytable: t], hour_fragment(t.interval, tz))
|> group_by([_], fragment("1"))
|> Repo.all()

That’s how I hacked around a similar ERROR 42803. I planned to open an issue on ecto repo about it but forgot.

1 Like
3

That absolutely works, and is much preferrable to what I was doing. I didn’t expect there to be such an easy solution. Thank you!

1 Like