DATE, TIME and TIMESTAMP comparison in "sort order".

[abhikuhikar]

Is this sort what is going to ultimately be used to implement order by?

If so does that mean that time, date, and timestamp don't sort according to how humans perceive time?

If so that seems counter intuitive - perhaps we should coerce everything to a UTC timestamp and then sort off that.

I believe that Partiql spec section 12.2 is under specified since it only has a single type timestamp and ought to be clarified before order by is implemented

Originally posted by @sunpeaksfivemile in https://github.com/partiql/partiql-lang-kotlin/issues/412#issuecomment-918729330

Based on the discussion we had, DATE could/should be coerced to TIMESTAMP for the comparison. But I am not sure how can we coerce TIME to TIMESTAMP.

[robcalhounjr]

Time in SQL isn't a "moment in time" (to quote the Ion spec), like a Date or a Timestamp, so it should not be coerced to Timestamp.

A related question is how PartiQL types, such as Time, that have no isomorphic Ion native type, are to be sorted. Other examples include Day and Month. Are they converted to a standard serialized Ion representation and sorted by that?

If so, then the serialization format must have a natural sort (Days should sort Thursday then Friday for example). If not, then any storage that needs to store ordered values needs to implement matching type-aware collation for them, which feels onerous.

[almann]

I don't think TIME should sort together with anything--it has no point in time meaning. I am even suspicious that even DATE would make much sense here since that isn't even a point in time. We should refer to what other implementations do and the SQL specification in terms of what we need to do.