[Fixed]-Migrate datetime w. timezone in PostgreSQL to UTC timezone to use Django 1.4

by

21👍

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:

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;
👤Erwin Brandstetter

Leave a comment