How can I change time column to include timezone in TimescaleDB?

Question

How can I change column type from timestamp to timestamptz in TimescaleDB?

I used ingestion script outflux to get data from InfluxDB to TimescaleDB. This created tables that are using timestamp as their time column.

CREATE TABLE conditions (
    time        TIMESTAMP       NOT NULL,
    location    TEXT              NOT NULL,
    temperature DOUBLE PRECISION  NULL
);

I tried to convert it to timestamptz using standart alter command

alter table conditions
alter column "time" type timestamptz using ("time"::timestamptz)

But that produced following error

ERROR:  check constraint "constraint_1007" of relation "_hyper_12_1007_chunk" is violated by some row
CONTEXT:  SQL statement " ALTER TABLE _timescaledb_internal._hyper_12_1007_chunk ADD CONSTRAINT constraint_1007 CHECK ( "time" >= TIMESTAMPTZ '2020-12-03 01:00:00+01' AND "time" < TIMESTAMPTZ '2020-12-10 01:00:00+01' ) "
PL/pgSQL function _timescaledb_internal.chunk_constraint_add_table_constraint(_timescaledb_catalog.chunk_constraint) line 42 at EXECUTE
SQL state: 23514

score -1 · Answer 1 · answered May 18 '21 at 07:42

You seem to think that timestamptz stores the timezone.
This is not true: None of the PostgreSQL types timestamp and timestamptz store the timezone.
They just treat the input differently: see this detailed explanation about both types.

Once you understand this, you may find that you don't need to convert timestamp to timestamptz
When you still want to convert the types, then this answer may help.

How can I change time column to include timezone in TimescaleDB?

1 Answers1