[SOLVED] Can the designated timestamp syntax using TIMESTAMP keyword automatically sort by ts?

Can the designated timestamp syntax using TIMESTAMP keyword automatically sort by ts?

Is it possible to make TIMESTAMP(ts) not only make ts be a designated timestamp, but also ensure the data is ordered by it?

At the moment, If I have unordered (or potentially unordered) data and need it to form a proper timeline I have to order first, then assign a designated timestamp.

SELECT * FROM (
  SELECT * FROM (
    SELECT '2025-04-02T01:00:00Z'::timestamp AS ts
    UNION ALL
    SELECT '2025-04-10T02:00:00Z'::timestamp AS ts
    UNION ALL
    SELECT '2025-04-03T03:00:00Z'::timestamp AS ts
    UNION ALL
    SELECT '2025-04-01T04:00:00Z'::timestamp AS ts
  )
  ORDER BY ts
)
TIMESTAMP(ts)

There are two issues with it:

It is a bit verbose, specially if I have subqueries.
If I forget order by then I end up with a table which has a designated timestamp on an column which is not actually ordered - that can probably lead to a disaster.

Solution

You can actually drop the TIMESTAMP(ts) bit for most cases in recent versions of QuestDB, as long as there is an ORDER BY over a timestamp column. QuestDB will understand that is the designated column for this case.

Your specific query above executes fine without the last part on QuestDB 8.3.0, for example.