oracle 在 Web 应用程序中处理时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33343893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Handling time zone in web application
提问by Gopi
In our web application we need to show and enter date time information for different countries in different time zone. Right now, we are maintaining separate web server and separate database (oracle 11g) for each country.
在我们的 Web 应用程序中，我们需要显示和输入不同时区不同国家的日期时间信息。目前，我们正在为每个国家/地区维护单独的 Web 服务器和单独的数据库（oracle 11g）。
We are planning to merge all into one portal with single database (oracle 11g). This portal should capture/display date and time in user local time zone.
我们计划将所有内容合并为一个具有单个数据库的门户（oracle 11g）。此门户应在用户本地时区捕获/显示日期和时间。
So far, I have searched about this, I got below suggestion.
到目前为止，我已经搜索过这个，我得到了以下建议。
1) set web server's and database server's time zone to UTC and while fetching data (data and time) convert into user local time zone.
1) 将 Web 服务器和数据库服务器的时区设置为 UTC，并在获取数据（数据和时间）时转换为用户本地时区。
If you suggest this approach then please clarify the following specific questions.
如果您建议采用这种方法，请澄清以下具体问题。
most of the time we are capturing date alone, is it require to capture date and time along with time zone always?
while storing date and time where we need to convert user local time zone to UTC in javascript/java/oracle?
while fetching date and time where we need to convert UTC to user
local time zone query itself/java/java script?many place we have reports to show based on date column such as
today/current month/date range.how we can handle this(input - user local time zone - database in UTC)?- which data type we have to use for date field (date/timestamp/timestamp with time zone/timestamp with local time zone)?
大多数时候我们单独捕获日期，是否需要始终捕获日期和时间以及时区？
在 javascript/java/oracle 中存储我们需要将用户本地时区转换为 UTC 的日期和时间时？
在获取日期和时间时，我们需要将 UTC 转换为用户
本地时区查询本身/java/java 脚本？我们有许多地方可以根据日期列显示报告，例如
今天/当前月份/日期范围。我们如何处理这个问题（输入 - 用户本地时区 - UTC 中的数据库）？- 我们必须为日期字段使用哪种数据类型（日期/时间戳/带时区的时间戳/带本地时区的时间戳）？
2) capture date and time in both user local time zone and UTC. Stored as separate columns, user local time zone will be used for display purpose and UTC will be used for business logic.
2) 在用户本地时区和 UTC 中捕获日期和时间。存储为单独的列，用户本地时区将用于显示目的，UTC 将用于业务逻辑。
If you suggest this approach then please clarify the following specific questions.
如果您建议采用这种方法，请澄清以下具体问题。
Is it common practice to store the user local time zone and UTC?
which column i have to check condition while fetching reports to show based on date column such as today/current month/date range?
which data type we have to use for date column
(date/timestamp/timestamp with time zone/timestamp with local time
zone)?
存储用户本地时区和 UTC 是常见的做法吗？
根据日期列（例如今天/当前月份/日期范围）获取要显示的报告时，我必须检查哪一列？
我们必须为日期列使用哪种数据类型
（日期/时间戳/带时区的时间戳/带本地时
区的时间戳）？
thanks in advance
提前致谢
回答by Basil Bourque
Read the Question Daylight saving time and time zone best practices. Yours is basically a duplicate.
阅读问题夏令时和时区最佳实践。你的基本上是重复的。
Servers in UTC
UTC 中的服务器
Yes, generally servers should have their OS set to UTCas the time zone, or if not provided use GMTor the Reykjavík Iceland time zone. Your Java implementation probably picks up this setting as its own current default time zone.
是的，通常服务器应将其操作系统设置为UTC作为时区，或者如果未提供，则使用GMT或雷克雅未克冰岛时区。您的 Java 实现可能会将此设置作为其自己的当前默认时区。
Specify time zone
指定时区
But do not depend on the time zone being set to UTC. A sysadmin could change it. And any Java code in any thread of any app within your JVM can change the JVM's current default time zone at runtimeby calling TimeZone.setDefault. So instead, make a habit of always specifying the desired/expected time zone by passing the optional argument in your Java code.
但不要依赖于设置为 UTC 的时区。系统管理员可以更改它。JVM 中任何应用程序的任何线程中的任何 Java 代码都可以在运行时通过调用TimeZone.setDefault. 因此，通过在 Java 代码中传递可选参数，养成始终指定所需/预期时区的习惯。
I consider it a design flaw that any date-time framework would make the time zone optional. Being optional creates endless amounts of confusion because programmers, like everybody else, unconsciously think in terms of their own personal time zone unless prompted. So all too often in date-time work no attention is paid to the issue. Add on the problem that the JVM default varies. By the way, ditto for Locale, same problems, should always be specified explicitly.
我认为任何日期时间框架都会使时区成为可选的设计缺陷。可选会造成无穷无尽的混乱，因为程序员和其他人一样，除非得到提示，否则会无意识地根据自己的个人时区进行思考。因此，在日期时间工作中经常没有注意这个问题。添加JVM默认值变化的问题。顺便说一句，Locale同样的问题，同样的问题，应该始终明确指定。
UTC
世界标准时间
Your business logic, data storage, and data exchange should almost always be done in UTC. Nearly every database has a feature for adjusting any input into UTC and storing in UTC.
您的业务逻辑、数据存储和数据交换应该几乎总是在UTC 中完成。几乎每个数据库都有一个功能，可以将任何输入调整为 UTC 并以 UTC 存储。
When presenting a date-time to a user, adjust into the expected time zone. When serializing a date-time value, use the ISO 8601string formats. See the Answerby VickyArora for Oracle specifically (I am a Postgresperson). Be sure to read the doc carefully, and practice by experimenting to fully understand your database's behavior. The SQL spec does not spell out very much in this regard, and behavior varies widely.
当向用户展示日期时间时，调整到预期的时区。序列化日期时间值时，请使用ISO 8601字符串格式。具体参见VickyArora 为 Oracle 提供的答案（我是Postgres人）。请务必仔细阅读文档，并通过试验来充分理解数据库的行为。SQL 规范在这方面没有详细说明，行为差异很大。
java.sql
sql语句
Remember that when using Java and JDBC, you will be using the java.sql.Timestampand related data types. They are always in UTC, automatically. In the future expect to see JDBC drivers updated to directly use the new data types defined in the java.time framework built into Java 8 and later.
请记住，在使用 Java 和JDBC 时，您将使用java.sql.Timestamp和相关数据类型。它们总是自动地使用 UTC。将来希望看到 JDBC 驱动程序更新以直接使用 Java 8 及更高版本中内置的 java.time 框架中定义的新数据类型。
java.time
时间
The old classes are outmoded by java.time. Learn to use java.timewhile avoiding the old java.util.Date/.Calendar and make your programming life much more pleasant.
旧类已被java.time过时。学习使用 java.time，同时避免使用旧的 java.util.Date/.Calendar，让您的编程生活更加愉快。
Until your JDBC driveris updated, you can use the conversion convenience methods built into java.time. See examples next, where Instantis a moment in UTC and ZonedDateTimeis an Instant adjusted into a time zone.
在您的JDBC 驱动程序更新之前，您可以使用 java.time 中内置的转换便利方法。请参见接下来的示例，其中Instant是 UTC 中的时刻，ZonedDateTime是调整到时区的 Instant 。
Instant instant = myJavaSqlTimestamp.toInstant();
ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );
To go the other direction.
去另一个方向。
java.sql.Timestamp myJavaSqlTimestamp = java.sql.Timestamp.from( zdt.toInstant() );
If you need original time zone, store it
如果您需要原始时区，请将其存储
If your business requirements consider the original input data's time zone to be important, to be remembered, then store that explicitly as a separate column in your database table. You can use an offset-from-UTC, but that does not provide full information. A time zone is an offset plusa set of rules for the past, present, and future handling of anomalies such as Daylight Saving Time. So a proper time zone nameis most appropriate such as America/Montreal.
如果您的业务需求认为原始输入数据的时区很重要，需要记住，则将其显式存储为数据库表中的单独列。您可以使用offset-from-UTC，但这不能提供完整信息。时区是一个偏移量加上一组规则，用于过去、现在和未来处理夏令时等异常。因此，正确的时区名称是最合适的，例如America/Montreal.
Date-only is ambiguous
仅日期不明确
You said you collect many date-only values, without time-of-day and without time zone. The class for that in java.time is LocalDate. As with LocalTimeand LocalDateTime, the “Local…” part means no particular locality, so therefore no time zone, and so not a point on the timeline -- has no real meaning.
你说你收集了许多仅限日期的值，没有时间和时区。java.time 中的类是LocalDate. 与LocalTime和 一样LocalDateTime，“本地...”部分表示没有特定的位置，因此没有时区，也没有时间线上的一个点——没有真正的意义。
Keep in mind that a date-only value is ambiguous by definition. At any given moment, the date varies around the world. For example, just after midnight in Paris Franceis a new day but in Montréal Québecthe date is still “yesterday”.
请记住，仅日期值的定义是不明确的。在任何特定时刻，世界各地的日期都不同。例如，法国巴黎的午夜刚过是新的一天，但魁北克蒙特利尔的日期仍然是“昨天”。
Usually in business some time zone is implicit, even unconsciously intuited. Unconscious intuition about data points tends not to work well over the long term, especially in software. Better to make explicit what time zone was intended. You could store the intended zone alongside the date such as another column in database table, or your could make a comment in your programming code. I believe it would vastly better and safer to store a date-time value. So how do we transform a date-only into a date-time?
通常在业务中，某些时区是隐含的，甚至是无意识的直觉。从长远来看，关于数据点的无意识直觉往往效果不佳，尤其是在软件中。最好明确说明预期的时区。您可以将预期区域与日期一起存储，例如数据库表中的另一列，或者您可以在您的编程代码中进行注释。我相信存储日期时间值会更好也更安全。那么我们如何将仅日期转换为日期时间？
Often a new day is the moment after midnight, the first moment of the day. You might think that means the time-of-day 00:00:00.0but not always. Daylight Saving Time (DST)and possibly other anomalies may push the first moment to a different wall-clock time. Let java.time determine the correct time-of-day for first moment going through the LocalDateclass and its atStartOfDaymethod.
新的一天通常是午夜过后的那一刻，也就是一天中的第一个时刻。您可能认为这意味着一天中的时间，00:00:00.0但并非总是如此。夏令时 (DST)和可能的其他异常可能会将第一个时刻推到不同的挂钟时间。让 java.time 确定通过LocalDate类及其atStartOfDay方法的第一时刻的正确时间。
ZoneId zoneId = ZoneId.of( "America/Montreal" );
LocalDate today = LocalDate.now( zoneId );
ZonedDateTime todayStart = today.atStartOfDay( zoneId );
In some business contexts a new day may be defined (or assumed) to be business hours. For example, say a publisher in New Yorkmeans 9 AM in their local time when they say “the book draft is due by January 2nd”. Let's get that time-of-day for that date in that time zone.
在某些业务环境中，新的一天可能被定义（或假定）为营业时间。例如，假设纽约的出版商在说“书稿将于 1 月 2 日到期”时表示当地时间上午 9 点。让我们获取那个时区中那个日期的时间。
ZoneId zoneId = ZoneId.of( "America/New_York" );
ZonedDateTime zdt = ZonedDateTime.of( 2016 , 1 , 2 , 9 , 0 , 0 , 0 , zoneId );
What does that mean for the author working in New Zealand? Adjust into her particular time zonefor presentation to her by calling withZoneSameInstant.
这对在新西兰工作的作者意味着什么？通过调用调整到她的特定时区以向她展示withZoneSameInstant。
ZoneId zoneId_Pacific_Auckland = ZoneId.of( "Pacific/Auckland" );
ZonedDateTime zdt_Pacific_Auckland = zdt.withZoneSameInstant( zoneId_Pacific_Auckland );
Database
数据库
For database storage we transform into an Instant(a moment on the timeline in UTC) and pass as a java.sql.Timestampas seen earlier above.
对于数据库存储，我们转换为 a Instant（UTC 时间线上的一个时刻）并作为 a 传递，java.sql.Timestamp如上文所述。
java.sql.Timestamp ts = java.sql.Timestamp.from( zdt.toInstant() );
When retrieved from the database, transform back to a New York date-time. Convert from java.sql.Timestampto an Instant, then apply a time zone ZoneIdto get a ZonedDateTime.
从数据库中检索时，转换回纽约日期时间。从 转换java.sql.Timestamp为Instant，然后应用时区ZoneId以获取ZonedDateTime.
Instant instant = ts.toInstant();
ZoneId zoneId = ZoneId.of( "America/New_York" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );
If your database drivercomplies with JDBC 4.2or later, you may be able to pass/fetch the java.timetypes directly rather than convert to/from java.sqltypes. Try the PreparedStatement::setObjectand ResultSet::getObjectmethods.
如果您的数据库驱动程序符合JDBC 4.2或更高版本，您可以直接传递/获取java.time类型，而不是转换为/从java.sql类型。试试PreparedStatement::setObject和ResultSet::getObject方法。
回答by Wolfgang
Use TIMESTAMP WITH LOCAL TIME ZONE
if you want the database to automatically
convert a time between the database and session time zones.
如果您希望数据库在数据库和会话时区之间自动转换时间，请使用 TIMESTAMP WITH LOCAL TIME ZONE 。
Stores a date and time with up to 9 decimal places of precision. This datatype is sensitive to time zone differences. Values of this type are automatically converted between the database time zone and the local (session) time zone. When values are stored in the database, they are converted to the database time zone, but the local (session) time zone is not stored. When a value is retrieved from the database, that value is converted from the database time zone to the local (session) time zone.
存储最多 9 个小数位精度的日期和时间。此数据类型对时区差异敏感。这种类型的值会在数据库时区和本地（会话）时区之间自动转换。当值存储在数据库中时，它们将转换为数据库时区，但不存储本地（会话）时区。从数据库中检索值时，该值将从数据库时区转换为本地（会话）时区。
回答by Muhammad Muazzam
Here I have clarified the following specific questions.
在这里，我澄清了以下具体问题。
Q.Most of the time we are capturing date alone, is it require to capture date and time along with time zone always?
问：我们大部分时间都是单独捕获日期，是否需要始终捕获日期和时间以及时区？
A.Yes
A.是的
Q.while storing date and time where we need to convert user local time zone to UTC in javascript/java/oracle?
Q.在 javascript/java/oracle 中存储日期和时间时，我们需要将用户本地时区转换为 UTC 吗？
A.Not convert during data save, save as it with source date+time+zone
A.数据保存时不转换，用源日期+时间+区域另存为
Q.While fetching date and time where we need to convert UTC to user local time zone query itself/java/java script?
问：在获取日期和时间时，我们需要将 UTC 转换为用户本地时区查询本身/java/java 脚本？
A.Always convert to display in local time zone OR UTC format where application opened.
A.始终转换为以本地时区或 UTC 格式显示应用程序打开的地方。
Q.Many place we have reports to show based on date column such as today/current month/date range.how we can handle this(input - user local time zone - database in UTC)?
问：我们有许多地方要根据日期列显示报告，例如今天/当前月份/日期范围。我们如何处理这个问题（输入 - 用户本地时区 - UTC 中的数据库）？
A.The system should provide setting option to user for date time display in what format, either local where application opened or UTC. All is done on front end only.
A.系统应该为用户提供日期时间显示的设置选项，以何种格式，应用程序打开的本地或UTC。一切只在前端完成。
Q.Which data type we have to use for date field (date/timestamp/timestamp with time zone/timestamp with local time zone)?
问：我们必须为日期字段使用哪种数据类型（日期/时间戳/带时区的时间戳/带本地时区的时间戳）？
A.Timestamp
A.时间戳
So in short, save datetime in source time zone and convert based upon user preferences either in local where page opened or UTC format. Means, conversion will be done through script for display only. The region where product is being popular can also be find.
简而言之，将日期时间保存在源时区中，并根据用户偏好以页面打开的本地或 UTC 格式进行转换。意味着，转换将通过脚本完成，仅用于显示。还可以找到产品流行的地区。
回答by Hey StackExchange
I would simply transform the existing dates stored in the DB into Long, and persist (ETL process) this Longvalue, along with the known (or deducted) pattern, Localeand TimeZone(default meta). And persist any new Dateas long as with the default meta.
我想简单地改变存储在数据库中到现有的日期Long，并持续（ETL过程）这个Long值，与已知的（或扣除）一起pattern，Locale和TimeZone（默认元）。Date只要使用默认元，就可以保留任何新内容。
ETL example
ETL 示例
Let say 2015-11-29 10:07:49.500 UTCis stored in the DB:
假设2015-11-29 10:07:49.500 UTC存储在数据库中：
// Known or deducted format of the persisted date
String pattern = "yyyy-MM-dd HH:mm:ss.SSS";
Locale locale = Locale.ENGLISH;
TimeZone zone = "UTC";
// Date to ms
SimpleDateFormat sdf = new SimpleDateFormat(pattern, locale);
sdf.setTimeZone(TimeZone.getTimeZone(zone));
Date date = sdf.parse(pattern);
// ETL: Can now be persisted in Long, along with default META (pattern, Locale, TZ)
Long dateL = date.getTime(); // for e.g. 1448827660720
...
The persisted Longvalue can also be transformed in any other format, if needed
Long如果需要，持久值也可以转换为任何其他格式
pattern | locale | tz | result
============================================
yyyy/MM/dd | null | null | 2015/11/29
dd-M-yyyy hh:mm:ss | null | null | 29-11-2015 10:07:40
dd MMMM yyyy zzzz | ENGLISH | null | 29 November 2015 Central European Time
yyyy-MM-dd HH:mm:ss.SSS | null | UTC | 2015-11-29 10:07:49 UTC
回答by fatihn
Feasible and logical approach is ; Convert user entered time to GMT/UTC +00 and store that in db with or without timezone identifier it does not matter. When you need to display the time to user convert GMT/UTC time in java to local time of user.
可行且合乎逻辑的方法是；将用户输入的时间转换为 GMT/UTC +00 并将其存储在带有或不带有时区标识符的 db 中，这无关紧要。当您需要向用户显示时间时，将 Java 中的 GMT/UTC 时间转换为用户的本地时间。
