使用或不使用 TZ 在 Postgres 中节省时间？
我在数据库中有一个列应该代表订购的截止日期。 我们假设该值为美国东部时间上午 11 点。 我在冬季将数据库保存为 UTC，因此将其保存为下午 4 点 UTC。 问题是，当 DST 开始时，转换回 EST 的时间是中午 12 点... 我该如何解决这个问题？在数据库级别？如果是这样，我将如何节省恒定时间并将其转换为正确的值？如果我使用带有 TZ 的时间戳，它会被读取为 UTC 下午 5 点吗？
谢谢。
I have a column in the DB that supposed to represent a deadline for ordering.
Let's assume the value is 11am EST.
I've saved in the DB as UTC during Winter so it was saved as 4PM UTC.
Problem is, when DST starts, the conversion back to EST was 12PM...
How can I solve this? on the DB level? if so how would I save a constant time and convert it to the correct value? If I use timestamp with TZ, will it then be read as 5PM UTC?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您描述的场景是由于使用两个不同的 UTC 偏移量（UTC-5 和 UTC-4）将未来时间转换为 UTC 并再次转换回来而引起的。一般来说，不应以 UTC 形式存储未来时间（无论是循环时间还是单次时间），除非原始参考点已经采用 UTC 形式。 “始终使用 UTC”建议仅适用于过去/现在的时间戳。它不适用于未来值。
PostgreSQL 有多种不同的日期/时间数据类型。 “带时区”类型将隐式转换为 UTC 或从 UTC 隐式转换。 “无时区”类型则不会。
就您而言，听起来您希望有一个仅时间值来描述每天应用的截止日期。因此，您应该为该字段使用
time（又名time without time zone）数据类型。然后，您还应该将 IANA 时区 ID 存储在单独的字符字段 (varchar (50)应该足够了）。对于美国东部时间，您将存储'America/New_York'。假设所有用户的截止时间基于相同的时区。如果您在他们的时区切断它，那么每个用户的时区可能会有所不同，您将需要确定用户的时区。在评估是否已过截止时间时，您将获取当前 UTC 时间戳并将其转换为目标时区的本地日期和时间，以获得该区域的当前日期和时间。然后，您将获得一份副本，并将时间部分替换为您的截止时间。然后比较这两个值以查看截止日期是否已过。
您还应该考虑第二天的截止日期何时重置。是严格按照当地日期吗？如果他们尝试在该截止日期之后订购，您是否不允许（如果是的话，您什么时候再次允许），或者它是否适用于下一个日期？只有您可以回答此类问题，因为它会根据您的业务需求而有所不同。
我描述的操作可以直接在 PostgreSQL 中完成，使用诸如
AT TIME ZONE等，但通常最好在应用程序层执行它们。大多数编程平台都具有操作日期和时间以及处理时区的功能。The scenario you described is caused by converting a future time to UTC and back again, using two different UTC offsets (UTC-5 and UTC-4). In general, one should not store future times (whether recurring or single-instance) in terms of UTC, unless the original reference point is already in terms of UTC. The advice "Always use UTC" applies only for past/present timestamps. It does not apply for future values.
PostgreSQL has several different date/time data types. The "with time zone" types will convert to/from UTC implicitly. The "without time zone" types will not.
In your case, it sounds like you would like to have a time-only value that describes the deadline that applies every day. Thus, you should use a
time(akatime without time zone) data type for that field. You should then also store the IANA time zone ID in a separate character field (varchar(50)should be sufficient). For US Eastern Time, you would store'America/New_York'. That assumes the cutoff is based on the same time zone for all users. If rather you are cutting it off at their time zone, then it could be different per-user, and you will need to determine the user's time zone.When evaluating whether the deadline has passed, you would take the current UTC timestamp and convert it to the local date and time in the target time zone to have the current date and time in that zone. You'd then take a copy of that and replace the time part with your deadline time. Then compare those two values to see if the deadline has passed.
You should also be thinking about when does the deadline reset for the next day. Is it strictly based on the local date? If they try to order past that deadline, do you disallow it (and if so when do you allow it again), or does it apply to the next date? Only you can answer such questions, as it will vary based on your business needs.
The manipulations I described could be done directly in PostgreSQL, using functions like
AT TIME ZONEand others, but generally you are better off doing them in your application layer. Most programming platforms have functions for manipulating dates and times, and for working with time zones.