Clojurians Log v2
xtdb
There seem to be some inconsistencies in the temporal filters for SQL and XTQL.
SQL has these https://docs.xtdb.com/reference/main/sql/queries.html#_temporal_filter:
• AS OF
• FROM
• BETWEEN
Some notes/questions:
• Is FROM/TO equivalent to BETWEEN/AND?
XTQL has these https://docs.xtdb.com/reference/main/xtql/queries.html#_from:
• at
◦ the documentation for this refers to two timestamps (`row-from <= timestamp < row-to`), but this filter only takes one, so I think this documentation is wrong
• from
• to
• in
SQL AS OF is equivalent to XTQL at, and SQL FROM/TO and BETWEEN/AND seem equivalent to XTQL in, but there does not seem to be any SQL equivalent to the XTQL from and to temporal filters.
Hey @esp1 🙂
> Is FROM/TO equivalent to BETWEEN/AND?
not quite - from/to is inclusive/exclusive, between/and is inclusive/inclusive
> the documentation for this refers to two timestamps (`row-from <= timestamp < row-to`), but this filter only takes one, so I think this documentation is wrong
timestamp is the one in the filter; row-from and row-to apply to the documents in your table - so either valid-from and valid-to or system-from and system-to, depending on whether it's a VT or ST filter
> there does not seem to be any SQL equivalent to the XTQL from and to temporal filters
from and to, used alone, really just create open-ended ranges, so they're equivalent to (in and (in nil respectively. There's no SQL equivalent for this, although IIRC we do accept NULL in FROM
HTH 🙂
that does help, thanks!
Hm, I think I found another bug. The XTQL to temporal filter does not appear to work...
tbh we really need to give XTQL some love, it's not been a focus for the last 12m or so while we've been building out the SQL/Postgres tooling support and more generally focusing on performance and execution stability 😕