java - 使用 JAVA 在数据库中存储日期/时间信息？

Question

存储日期/时间信息(以及一般时间戳)以便可以在不同时区显示的信息的最佳方式是什么？

我的想法是让数据库以指定格式(如 GMT 或 UTC)保存时间。从数据库读取时，将收集到的任何日期/时间/时间戳数据转换为该区域的本地时间戳。

将数据插入数据库时​​。以下将适用。

1. Collect the time from the User Interface as a string
2. convert the time to the local date/time representation (where the timezone is included)
3. convert the local date/time to GMT (or UTC)
4. Save to the DB in GMT / UTC format

我看过 Joda Time 包，也考虑过使用它。

人们解决这个问题的通常方法是什么？我希望这不是太含糊——我已经看到 Oracle DB 在这里保存日期/时间，时区似乎是本地的。

例如:我得到以下信息:

SQL> SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)||':'||
       EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP)
FROM dual;  
Result  => -5:0

任何有关此问题的提示都将不胜感激。

TIA

更新

完成了以下操作:

SQL> ALTER DATABASE SET TIME_ZONE = 'UTC';

系统被“反弹”

SQL>  select dbtimezone from dual;
DBT
---
UTC

出于好奇，您如何向数据库表中插入数据，以便将时区考虑在内？对列使用 TIMESTAMP 类型时出现错误:

SQL> create table shot (t timestamp);
Table created.

SQL> insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland');
insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland')
                         *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL>  insert into shot values( '26-FEB-09 11.36.25.390713 AM');
1 row created.

更新还添加了有关在 java/jdbc/Oracle 中设置“ session 时区”的旧帖子的链接。如果我理解正确，则需要某个 .jar 文件来确保在连接到数据库以运行 Java 代码时“ session 时区”与“数据库时区”相同。

Setting session timezone with spring jdbc oracle

Answer 1

我建议使用数据类型 TIMESTAMP WITH LOCAL TIME ZONE。TIMESTAMP WITH LOCAL TIME ZONE 的所有值都在内部存储在 DBTIMEZONE 中，在您的情况下为 UTC(因此一旦插入，您就不能再更改 DBTIMEZONE此数据类型的任何数据)。当您选择此类列时，时间将始终显示在当前用户 session 时区 SESSIONTIMEZONE 中。因此，正确设置 session 时区非常重要。

当您运行 insert into shot values( '26-FEB-09 11.36.25.390713 AM Pacific/Auckland'); 时，您会收到一个错误，因为 '26-FEB-09 11.36。 25.390713 AM Pacific/Auckland' 是一个字符串 - 不是时间戳!

您可以通过多种方式插入时间戳值，例如:

• TIMESTAMP '2009-02-16 23:36:25.390713 Pacific/Auckland'
• TO_TIMESTAMP_TZ('26-FEB-09 11.36.25.390713 AM Pacific/Auckland', 'DD-MON-RR HH:MI:SS.FF AM TZR')
• FROM_TZ(TIMESTAMP '2000-03-28 18:00:00', '太平洋/奥克兰')
• FROM_TZ(TIMESTAMP '2000-03-28 18:00:00', SESSIONTIMEZONE)(尽管您可以跳过它，因为 SESSIONTIMEZONE 是默认值)
• FROM_TZ(TO_TIMESTAMP('26-FEB-09 11.36.25.390713 AM', 'DD-MON-RR HH:MI:SS.FF AM'), '太平洋/奥克兰')

小心这些表达式:

• TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'Pacific/Auckland'
• TO_TIMESTAMP('26-FEB-09 11.36.25.390713 AM', 'DD-MON-RR HH:MI:SS.FF AM') AT TIME ZONE 'Pacific/Auckland'<

TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'Pacific/Auckland' 实际上意味着 (FROM_TZ(TIMESTAMP '1999-10-29 01:30: 00', 'SESSIONTIMEZONE') AT TIME ZONE 'Pacific/Auckland'，因此您可能会遇到不必要的时移。

请注意，当您使用 TIMESTAMP literal 时然后格式固定在 YYYY-MM-DD HH24:MI:SS

如果您未指定任何时区信息(例如 TIMESTAMP '2009-02-16 11:36:25')，则 Oracle 会将其视为当前用户 session 时区 session 时区。

• SYSTIMESTAMP 返回数据库服务器操作系统时区的当前时间，而不是 DBTIMEZONE - 尽管它们通常设置为相同的值。

• CURRENT_TIMESTAMP 返回当前用户 session 时区的当前时间。数据类型是 TIMESTAMP WITH TIME ZONE。

• LOCALTIMESTAMP 还返回当前用户 session 时区的当前时间，但是数据类型是 TIMESTAMP，而不是 TIMESTAMP WITH TIME ZONE .

另一个注意事项，当你有 TIMESTAMP WITH LOCAL TIME ZONE 时，你不能像这样格式化输出 SELECT TO_CHAR(t, 'DD/MM/YYYY HH24:MI:SS ZTR') FROM shot 因为根据定义 TIMESTAMP WITH LOCAL TIME ZONE always 显示在当前用户 session 时区和 TZR(即时区区域名称)没有任何意义。好吧，您可以将此视为 Oracle 错误，但在某种程度上它是有道理的。