Sample Header Ad - 728x90

Convert Postgres TIMESTAMP to TIMESTAMPTZ

35 votes
2 answers
43617 views
I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP WITHOUT TIME ZONE, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so want to convert the field to TIMESTAMP WITH TIME ZONE. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration *should* be trivial, but I was wondering if there are any things complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?
Asked by GTF (453 rep)
Apr 5, 2016, 11:18 AM
Last activity: Oct 6, 2023, 09:57 PM