17

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jens Lundstrom
  • 702
  • 8
  • 15

1 Answers1

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
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Correct, some of my wording was off. When I browsed the data in Postgress using web interface I see my timestamp w. tz as '2011-05-24 11:17:11.533479-05' and I think the last part as in -05 shows the offset so it is not stored in UTC (or is this just the webinterface that adapts to the fact that I am logged in and my tz is -05'? – Jens Lundstrom May 05 '12 at 14:15
  • @JensLundstrom: Time zone offset is not even *saved*. What you see is the *display* according to the local time zone setting. I added some queries to illustrate. – Erwin Brandstetter May 05 '12 at 14:34
  • 1
    Don't forget, when you upgrade to 1.4, you have to set [`USE_TZ = True`](https://docs.djangoproject.com/en/dev/ref/settings/#use-tz) for the new behavior to kick in! – Filip Dupanović May 05 '12 at 18:44
  • Yep, have done that. I am also running django-timezones for some decorators and so far the testing of everything is going ok. For anyone else doing this migration; (i) go over code and replace datetime.datetime.now() with the django utils now() and (ii) do some smoke testing of anything that sets or compares with dates and times, I had more naive datetime occurrences then what I thought. – Jens Lundstrom May 06 '12 at 12:20