How can I change the designated timestamp in QuestDB

Question

I have a table with a designated timestamp col A. How can I change the designated timestamp from col A to col B? Is it possible at all?

Answer 1

QuestDB does not support changing the designated column directly. However, you can create a new table based on the old one, specifying a different designated timestamp column.

Example:

Suppose you have a table named readings with a designated column ts. This table could have been created as follows:

CREATE TABLE readings
AS(
    SELECT
        rnd_uuid4() ID,
        timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,0) * 100000L) ts,
        rnd_timestamp(to_timestamp('2015', 'yyyy'), to_timestamp('2016', 'yyyy'), 0) ts2,
        rnd_double(0)*8 + 15 temp,
        rnd_long(0, 10000, 0) sensorId
    FROM long_sequence(10000000))
TIMESTAMP(ts)
PARTITION BY MONTH WAL;

To change the designated column from ts to ts2, you need to:

1. Create a new table based on the old one.
2. Rename or drop the old table.
3. Rename the new table to the original name.

Here's how you can create the new temporary table:

CREATE TABLE readings_tmp AS (
  SELECT * FROM readings)
TIMESTAMP(ts2) PARTITION BY HOUR WAL;

Note that I specified ts2 as the designated timestamp column, whereas the original table used ts. I also changed the partitioning scheme: the old table was partitioned by MONTH, while the new one is partitioned by HOUR.

Once the table is created, you can either drop or rename the old table. You might choose to simply rename it, keeping it as a backup: RENAME TABLE readings TO readings_backup;

Finally, rename the newly created table to make it available under the original name: RENAME TABLE readings_tmp TO readings;

At this point, the readings table will have a new designated timestamp column.