在Django / Postgres中存储壁钟日期时间 [英] Storing wall-clock datetimes in Django/Postgres

问题描述

我想在Django中保存事件的未来挂钟日期时间（我的时区字符串单独存储）。

I want to save a future wall-clock datetime for events in Django (I have timezone string stored separately).

我不能简单地使用 DateTimeField ，因为它会强制执行带时区的时间戳，并始终节省当前时区的时间。它不能处理DST或当前日期与实际事件日期之间可能的时区更改。

I can't simply use the DateTimeField because it enforces timestamp with time zone and always saves time in current timezone. It doesn't handle DST or possible timezone changes between current date and the date of actual event.

我可以使用以下任一选项：

I could use any of these options:

• 选择任何时区来存储时间戳，并始终在使用Python中的实际时区之前将其丢弃。


• 将时间戳拆分为 DateField 和 TimeField 。


• 将日期时间存储为字符串。


• 自定义字段，用于将日期时间存储为不带时区的时间戳。

• Pick any timezone to store timestamps and always throw this timezone away before applying actual timezone in Python.
• Split timestamp to DateField and TimeField.
• Store datetime as string.
• Custom field that stores datetime as timestamp without time zone.

但是它使查询更加困难，而且看起来很怪异。

but it makes queries more difficult and seems quite weird.

我有没有更好的选择？这个用例似乎很常见，所以我想有一种更好的方法吗？

Are there any better options I miss? This usecase seems quite common so I guess there is a better way to do that?

编辑：我的用例：

假设我的用户想预订2019年12月20日10:00的约会，当前是2019年3月10日。我知道该用户的时区（以美国/东部之类的字符串形式单独存储）。

Let's say my user want to book an appointment to 2019-12-20 10:00 and currently it's 2019-03-10. I know the timezone of this user (it's stored separately as string like 'US/Eastern').

如果我假设EST从2019年11月3日开始，我能做的最好的就是将时间戳存储到 2019-12-20 15： 00：00 + 00：00 （或 2019-12-20 10：00-05：00 。我不想这样做，因为：

If I assume that EST starts at November 3, 2019, the best I can do is to store timestamp to 2019-12-20 15:00:00+00:00 (or 2019-12-20 10:00-05:00. I don't want this because:

• 我不知道我的tzdata是否具有将来日期时间的正确信息


• 即使当前确实，我不知道美国/东部时区是否会发生意外变化，如果不是美国，则会变得更糟。不能保证将来的DST更改。


• 如果用户使用其他时间时区，我必须在计算DST的同时重新计算每个约会。


• 如果在重新计算过程中tzdata发生了变化，请不要考虑。

• I have no idea if my tzdata has correct information for future datetime
• Even if it currently does, I have no idea if there would be any unexpected change in US/Eastern timezone and it becomes worse when it's not US. Future DST changes are not guaranteed.
• If user moves to different timezone, I'll have to recalculate every single appointment while taking care about DST.
• If tzdata changes during this recalculation... let's not think about that.

我希望将将来的日期存储为朴素的datetime +时区字符串，例如 US / Eastern，并且（几乎）从不构造任何日期的tz感知日期时间超过一周。Django + postgres当前强迫我在 timestamp中使用t ime zone ，它非常适合日志和过去的事件，但是它具有固定的偏移量（甚至没有时区名称），因此它不适合将来的挂钟日期时间。

I'd prefer to store future dates as naive datetime + timezone string like 'US/Eastern' and (almost) never construct tz-aware datetime for any date further than a week. Django + postgres currently forces me to use timestamp with time zone, which is great for logs and past events, but it has fixed offset (not even timezone name) so it doesn't fit for future wall clock datetimes.

在这个用例中，我不在乎时间不明确：不想在02:00 AM预订的用户。

For this usecase, let's say that I don't care about ambiguous times: not much users want to book at 02:00 AM.

推荐答案

我看到一些可能的解决方案：

I see a few possible solutions:

1. 设置 USE_TZ = False 和 TIME_ZONE ='UTC'并使用日历时间。不会进行任何转换，因此从本质上讲，您只是存储日历时间并将其恢复为原始日期时间。主要问题是此设置是全局设置，对于许多用途而言都不是一个很好的设置（例如 auto_now ）。

1. Set USE_TZ = False and TIME_ZONE = 'UTC' and use calendar times. No conversions will be done, so essentially you're just storing the calendar time and getting it back as a naive datetime. The main problem is that this setting is global, and is not a good one for many uses (e.g. auto_now).

如上，但设置 USE_TZ = True 。只要您以UTC表示日历时间，就不会有任何不愉快的转换。这里的问题是，您将了解日期时间，因此您必须小心忽略或删除任何地方的时区。

As above, but set USE_TZ = True. As long as you express your calendar times in UTC, there won't be any untoward conversions. The problem here is that you'll be getting aware datetimes, so you'll have to take care to ignore or remove the time zone everywhere.

使用单独的 DATE_FIELD 和。根据您要运行的查询类型，这可能不是一个好的解决方案。

Use separate DATE_FIELD and TIME_FIELD. This may or may not be a good solution depending on what kind of queries you're trying to run.

创建您自己的使用<$ c $的字段c>没有时区的时间戳。 （或者也许已经存在？）

Create your own field that uses timestamp without time zone. (Or perhaps it already exists?)

请注意，此问题与过去和将来无关。这是关于要使用固定时间而非日历（或挂钟）时间。您提出的观点肯定是反对使用时间来代表日历时间的。

Note that this issue has nothing to do with past versus future. It's about wanting to use a fixed moment in time versus a calendar (or wall clock) time. The points you raised are certainly valid objections to using a point in time to represent a calendar time.

这篇关于在Django / Postgres中存储壁钟日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！