You are correct: this is unfortunately not a valid query in Cockroach because the f0 alias isn’t pinned to the table – it gets pinned to the AS OF SYSTEM TIME expression, and that’s not valid syntax. The proper syntax would look like this:
`SELECT f0."id" FROM users AS f0` AS OF SYSTEM TIME $1
i.e. the AS OF SYSTEM TIME bit comes at the end without any AS alias following.
What about subqueries? Will this work?
SELECT f0."id" FROM (SELECT * FROM users AS OF SYSTEM TIME $1) AS f0
Another idea is to go from the opposite side. Here is a code that shows the direction 
:
query = from u in User, where: ...
defmodule MyApp.Repo do
def all_as_of_system(query) do
{sql, params} = to_sql(:all, query)
query!("SELECT * FROM (#{sql}) AS data AS OF SYSTEM TIME '5m'", params)
end
end
but this approach has a couple of downsides:
query argumentRepo.all/2, like paginator, dataloader, etc.
Ecto.Adapters.Postgres.Connection has a function that looks promising:
defp from(%{from: %{source: source, hints: hints}} = query, sources) do
{from, name} = get_source(query, sources, 0, source)
[" FROM ", from, " AS ", name | Enum.map(hints, &[?\s | &1])]
end
That appears to be where you might able to tweak the from. I’m gonna kick some tires in a custom adapter and see what I can learn.
Actually, you found something else. Have a look at hints argument. It is placed right after declaration of an alias.
This keyword is used by Tds adapter Ecto.Adapters.Tds — Ecto SQL v3.14.0
and looks like this is what is needed:
from u in User, hints: ["AS OF SYSTEM TIME '-5m'"]
but if you call Repo.all, then you’ll see the error
** (Ecto.QueryError) table hints are not supported by PostgreSQL
and here patching the adapter may help.
Ding! I got this to work by leveraging the :hints option as you suggested. I ended up defining a custom Ecto adapter which was a complete copy of the Ecto.Adapters.Postgres.Connection module except for the from/3 function. I modified from/3 to this:
defp from(%{from: %{hints: hints, source: source}} = query, sources) when hints != [] do
{from, name} = get_source(query, sources, 0, source)
from = [" FROM ", from, " AS " | name]
[from | hints]
end
defp from(%{from: %{source: source}} = query, sources) do
{from, name} = get_source(query, sources, 0, source)
[" FROM ", from, " AS " | name]
end
I also made a full copy of Ecto.Adapters.Postgres (no changes).
In my Repo (i.e. the module that use’s Ecto.Repo), I referenced the new adapter, e.g.
use Ecto.Repo,
otp_app: :foo,
adapter: Ecto.Adapters.CockroachDB
This made things work if I was importing Ecto.Query and supplied a :hints option. I took things a bit further and I overrode the prepare_query/3 function in my Ecto Repo:
def prepare_query(_operation, %{from: %{hints: []}} = query, opts) do
case Keyword.get(opts, :hints, []) do
[] -> {query, opts}
hints -> {put_in(query.from.hints, hints), opts}
end
end
def prepare_query(_operation, query, opts), do: {query, opts}
This let me do more “simple” lookups such as:
Repo.all(MySchema, hints: " AS OF SYSTEM TIME '-5m'")
Note that you have to leave a space before AS.
This all seems to work and it generates queries in the proper syntax, e.g.
[debug] QUERY OK source="my_table" db=6.5ms queue=6.9ms idle=1334.0ms
SELECT c0."id", c0."foo", c0."bar" FROM "foobar" AS c0 AS OF SYSTEM TIME '-5m' []
Probably this could be cleaned up, but would this be worthwhile as a standalone package/adapter?
I was thinking to ask you why, as according to
hints are already there and even with space before each hint.
I see that this code is present in master.
Seems like a new adapter is not needed, just use ecto_sql from master 
Haha! Glad to know we were on the right track. It looks like that change is on track to be included with the next tagged release – 3.11 from the looks of it.
The override of the prepare_query/3 might still be useful (but nothing there to commit to the repo, just to add for our personal preferences)