0

I used to insert timestamp into my PostgreSQL table in local time. Now I started to use UTC and I need to find a way to convert all dates/times already inserted to UTC time. How am I supposed to do that?

Learner
  • 51
  • 6
  • possible duplicate of [PostgreSQL - how to render date in different time zone?](http://stackoverflow.com/questions/10797720/postgresql-how-to-render-date-in-different-time-zone) –  Mar 09 '15 at 06:22
  • The normal way would be to read out the records with non UTC, change them to UTC and write back. Did you add a column to the table to indicate new values, or have some other (minimum record number) based on which you can make the destincation between old and new timestamps? Update your post by editing in this extra information (don't comment on the comment, instead make the question more complete (but leave out **Edit** or **Update** from your change)s) – Anthon Mar 09 '15 at 07:14
  • 1
    What is the data type of the column? `timestamp` or `timestamp with time zone`? – Clodoaldo Neto Mar 09 '15 at 10:14

1 Answers1

1

Pretty easy, in timestamp with time zone, all times are already stored in UTC, the conversion is done when you insert or select them (hours are added or subtracted). The safest way to remove the "with time zone" attribute would be to set your timezone to UTC, create a new field that is timestamp (without time zone) and set the data correctly, then drop the old field, re-create it without time zone, then copy the data back in, dropping the column. Seems like a lot of work, but you could verify your results along the way, if you needed to do any updates or back out altogether, you'd know that there was no chance for data loss.

Joe Love
  • 5,594
  • 2
  • 20
  • 32