仅以UTC格式存储日期是否有意义？ [英] Does it make sense to store date only fields in UTC?

问题描述

我们正在将系统转换为将所有日期字段存储为UTC。对于用于存储日期时间或时间的字段，这是个好主意。但我不相信它适用于仅限日期字段。

We are in the process of converting our system to store all date fields as UTC. That's a great idea for fields used to store datetime or time only. But I'm not convinced that it would work for date only fields.

例如：

用户输入的日期为5/25 / 11 EST，它被转换为UTC&存储在数据库中的时间为5/25/11 04:00。

A user enters a date as 5/25/11 EST, it is converted to UTC & stored in the db as 5/25/11 04:00.

CST中的另一个用户读取日期，它从UCT转换为CST&现在是5/24/11 23:00，但只显示日期（5/24/11）。

Another user in CST in reads the date, it is converted from UCT to CST & is now 5/24/11 23:00, but only the date is displayed (5/24/11).

是否有任何标准或最佳做法来处理仅限日期的字段和放大器; UTC？

Is there any standard or best practice for dealing with date only fields & UTC?

注意：遗憾的是，所有字段（无论是datetime，仅限时间还是仅日期）都定义为我们SQL数据库中的日期时间。

Note: unfortunately all the fields whether datetime, time only or date only are defined as datetime in our SQL db.

 

推荐答案

是的，存储时间不重要的日期在sql server中有点令人讨厌。

Yep, storing dates where the time is not significant is a bit of a nuisance in sql server.

在过去，我有一个日历表，将年月日翻译成一个整数。

In the past I've had calendar tables which translated year month day into an integer.

用一个整数表示在这些表中用作外键的日期它应该只有一个日期而不是时间。

With an integer to identify a day used as a  foreign key in those tables which should just have a date rather than time.

日历表还允许您标记银行假日，周末日等，以便您可以轻松地计算工作日。

The calendar table also allows you to mark bank holidays, week end days etc so you can do calculations of business days easily.

您还可以轻松地按日，周，会计期间进行总计，并在中间进行操作，而无需担心时区。

You can also easily total by day, week, accounting period and do inbetween without worrying about timezones.

 

只是全球商业报道的一部分乐趣。

Just part of the fun for global business reporting.

这篇关于仅以UTC格式存储日期是否有意义？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！