迁移datetime w。时区在PostgreSQL到UTC时区使用Django 1.4 [英] Migrate datetime w. timezone in PostgreSQL to UTC timezone to use Django 1.4

问题描述

我需要将Postgres中的所有现有日期时间从当前时区转换为UTC，否则Django 1.4时区在读取不是UTC的东西时可以理解（但是另存为UTC）。

我有一个非常好的和工作的Django 1.3 / Postgres网站，我处理多个时区。我正在存储所有的datetime w。时区信息，但这恰好设置为美国/东部时区（是的，我知道应该是UTC）在Postgres。

现在，我计划升级到Django 1.4和计划在那里使用Django时区支持。从我的理解，它将保存UTC中的所有datetime到数据库，这很好，但是我所有现有的信息时区设置，但不是UTC。

有没有人根据这个迁移，它是如何工作的，感觉就像我没有任何问题，或者我需要迁移大量的datetime数据。 p>

解决方案

我正在存储所有的datetime w。时区信息，但这恰好是在美国/东部时区设置
（是的，我知道应该是UTC）在Postgres。

PostgreSQL中的数据类型称为 timestamp 。没有名为datetime的类型。

timestamp 是 没有时区的时间戳 。

timestamptz 是 时区戳与时区的缩写 。

手册通知：

timestamp 的值存储为2000/01/01之前或之后的秒数。

类似于Posix时间，从30年前的 Unix epoch 1970-01-01 00:00 UTC开始。对于 timestamp ，假设本地 2000-01-01 00:00 。对于 timestamptz ，引用是 2000-01-01 00:00 UTC ，并调整值的时区偏移量输入和输出。

带有时区的时间戳记只是另一种方式来输入和表示一个独特的时间点。您不能将时间戳（带或不带时区）设置为任何其他时区（UTC）。时区偏移本身是不保存。它仅用于将值调整为UTC。

时间戳值的表示将采用当前时区设置， p>

• 相应地显示值（输出）


• 解释一个时间戳，时区（输入）。

好消息：您的迁移应刚刚开箱即用 - 只要你不要把它搞死。

我已经写了一个详细解释如何Postgres时间戳使用 此相关答案 中的示例和链接。

---

示例查询

尝试以下语句（一次一个块）。也可以使用你的列：

  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'; 
 
重置时区; 
  

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.

解决方案

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 values get adjusted for the time zone offset on input and output.

timestamp with time zone is just another way to input and represent 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 the value to UTC.

The representation of the timestamp value will take the current time zone setting into account

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

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

I have written a detailed explanation of how Postgres timestamps work with examples and links in this related answer.

---

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;

这篇关于迁移datetime w。时区在PostgreSQL到UTC时区使用Django 1.4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！