Migrate datetime w. timezone in PostgreSQL to UTC timezone to use Django 1.4

Question

Do I need to convert all existing datetime in Postgres from their current timezone to UTC or will Django 1.4 timezone understand when reading something that is not UTC (but then save as UTC).

I have a perfectly fine and working Django 1.3/Postgres web site where I handle multiple timezones. I am storing all datetime w. timezone info but this happens to be set to US/Eastern timezone (yes, I know should have been UTC) in Postgres.

Now, I plan upgrade to Django 1.4 and plan to use the Django timezone support there. From what I understand it will save all datetime in UTC to database and this is fine, but what about all my existing information where timezone is set but not to UTC.

Has anyone migrated according to this and how did it work, feels like either I have no problem at all or I need to migrate lots and lots of datetime data.

Answer 1

I am storing all datetime w. timezone info but this happens to be set to US/Eastern timezone (yes, I know should have been UTC) in Postgres.

There are a couple of misconceptions here.

The data type is called timestamp in PostgreSQL. There is no type called "datetime".
timestamp is short for timestamp without time zone.
timestamptz is short for timestamp with time zone.

As the manual informs:

timestamp values are stored as seconds before or after midnight 2000-01-01.

Similar to Posix time, which start 30 years earlier at the Unix epoch 1970-01-01 00:00 UTC. For timestamp, the local 2000-01-01 00:00 is assumed. For timestamptz the reference is 2000-01-01 00:00 UTC and display gets adjusted for the time zone offset of the current session on input and output.

timestamp with time zone stores a unique point in time. You cannot "set" a timestamp (with or without time zone) to any other time zone than UTC internally. The time zone offset itself is not saved at all. It is only used to adjust input / output to UTC.

The representation of the timestamp value takes the time zone setting of the current session into account.

• to display the value accordingly (output).
• to interpret a timestamp without time zone correctly (input).

The good news: your migration should just work out of the box - as long as you don't screw it up actively.

Detailed explanation of how Postgres timestamps with examples and links:

• Ignoring time zones altogether in Rails and PostgreSQL

Example queries

Try the following statements (one block at a time). And try it with your column, too:

SHOW timezone;

SELECT '2011-05-24 11:17:11.533479-05'::timestamptz(0);
SELECT '2011-05-24 11:17:11-05'::timestamptz;

SET timezone='UTC';
SELECT '2011-05-24 11:17-05'::timestamptz;

SELECT '2011-05-24 11:17-05'::timestamptz AT TIME ZONE 'UTC';

SELECT '2011-05-24 11:17-05'::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'UTC';

RESET timezone;