How do I change a column from "timestamp with time zone" to "date" in a hypertable?


I’m working on a Django application that utilizes TimescaleDB, and I’ve encountered an issue with altering a column type in one of my hypertables. Specifically, I have a column with the timestamp with time zone data type, and I want to change it to the date data type, truncating the time portion and retaining only the date for the existing data in that column.

I’ve attempted to perform this alteration through a Django migration, but the hypertable structure has caused complications. Same goes for how you’d normally achieve this with SQL queries for a regular postgres table. I also tried creating a new column with the DATE type, copying the data, and then deleting the old timestamp with time zone column, but I was met with the following error:

[42P16] ERROR: cannot drop column named in partition key Detail: Cannot drop column that is a hypertable partitioning (space or time) dimension.

I’ve looked through the docs but unless I’m missing something there doesn’t seem to be a solution to this problem in there.

is there a standard way to do this?


Edit: I solved it by creating a non-hypertable version of the table, altering it there and then converting it to a hypertable again and dropping the old table but this seems convoluted.

1 Like

That’s nice that you found this way to overcome! That was going to be my suggestion as well! thanks for sharing!