将 GMT 转换为本地日期时间
3.60/5 (4投票s)
SQL 函数，用于将 GMT 日期时间转换为本地日期时间
引言
几乎所有启用了国际化的应用程序都会将日期时间列保存在 UTC/GMT 时区，并根据用户的时区偏好将日期时间转换为本地时区。
此项目包含一个 SQL 函数 dbo.get_local_datetime (datetime, time_zone)，用于将数据库中基于 GMT 的日期时间值转换为本地time_zone。SQL 函数依赖于一个包含按时区 ID 划分的时区偏移量的表。此表由timezone_offsets.sql创建和填充。
该项目还包含一个 jar 文件，用于生成timezone_offsets.sql时区偏移量 SQL 脚本，以获取所需的年份。默认情况下，它生成从 1995 年到 2050 年的信息。Jar 的源代码也包含在 jar 文件中。只需阅读readme.txt即可开始使用。
涵盖的时区
关于企业应用程序应提供多少个时区，存在不同的看法。例如，Microsoft Windows 列出了所有主要时区，如果计算，大约有 74 个。虽然这是微软设定的标准，但许多应用程序需要支持这些时区的子集。Java 基于全球主要城市，涵盖了广泛的时区子集。
由timezone_offsets.sql生成的此表涵盖了 JDK 6.0 支持的所有时区的 599 个子集。它还考虑了 2005 年能源政策法案 (EPAct) 的任何变更或过去可能进行的任何其他变更以及未来可能的变更。您可以使用 jar 应用程序生成有限年份或默认年份的时区偏移量 SQL 脚本。因此，如果下一个 Java 版本添加了 200 个额外的子时区，您只需要重新生成此脚本。
Using the Code
您只需将timezone_offsets.sql脚本运行到您的数据库即可！
您已完成。您可以运行run_test.sql来测试结果。
要生成时区偏移量 SQL 脚本，您需要通过命令提示符调用 jar，或直接双击 jar。但是，要生成有限期间的脚本，您可能需要通过命令提示符调用 jar 并为startyear和endyear传递参数。
命令示例
java -jar timezoneoffset.jar 2000 2015
代码工作原理
脚本生成代码基本上会遍历时区数组。它使用 java.util.Calendar 和 java.util.TimeZone，将 Calendar 初始化为“startyear”或默认 1995 年的某一天，逐日递增，查找与 GMT 和本地时区之间的偏移量，并在找到差异时生成 SQL INSERT 语句。这会一直进行到达到“endyear”。
SQL 函数根据输入日期和时区 ID 查找相对偏移量，并返回所需时区的本地时间。
让我们先来看看 SQL 函数
create function dbo.get_local_datetime
(@date datetime, @time_zone varchar(25))
returns datetime as
BEGIN
declare @local_time datetime
declare @offset_time int
select @offset_time = offset from timezone_offsets _
where @date between start_time_gmt and end_time_gmt and time_zone_id = @time_zone
set @local_time = dateadd(ms, isnull(@offset_time,0), @date)
return @local_time
END
dbo.get_local_datetime 函数非常简单。它接受所需输出的时区 ID 和要转换的日期时间。它所做的就是根据输入日期从timezone_offsets表中查找偏移量，然后调用 SQL 中内置的dateadd函数来添加偏移量。如果偏移量为负数，则时间会减少，否则会增加。在执行此操作时，它还会通过使用isnull包装偏移量来检查传入的时区 ID 是否正确。如果您愿意，可以在此处进行修改，以便在传入无效时区 ID 时返回null或空string。我还为timezone_offsets表添加了适当的索引tz1，以确保性能不受影响。
现在让我们看看生成timezone_offsets表数据的 Java 代码。我将仅在此处显示并解释核心函数区域。
public static StringBuffer getTZinfo() {
StringBuffer sb = new StringBuffer();
int goCommandCounter = 0;
TimeZone gmt = TimeZone.getTimeZone("GMT");
DateFormat dfgmt59 = new SimpleDateFormat("yyyy-MM-dd HH:mm:59");
dfgmt59.setTimeZone(gmt);
DateFormat dfgmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
dfgmt.setTimeZone(gmt);
Calendar starttime = Calendar.getInstance(gmt);
Calendar endtime = Calendar.getInstance(gmt);
starttime.set(startyear, 0, 1, 0, 0, 0);
endtime.set(endyear, 0, 1, 0, 0, 0);
Calendar c1 = Calendar.getInstance(gmt);
Calendar tempcal = Calendar.getInstance(gmt);
String[] x = TimeZone.getAvailableIDs();
for (String tzid : x) {
TimeZone localzone = TimeZone.getTimeZone(tzid);
if (!localzone.useDaylightTime()) {
sb.append("INSERT timezone_offsets
(time_zone_id,start_time_gmt,end_time_gmt,offset) \n");
sb.append("VALUES('" + localzone.getID() + "','" +
dfgmt.format(starttime.getTime()) + "','" +
dfgmt59.format(endtime.getTime()) + "'," +
localzone.getOffset(tempcal.getTimeInMillis()) + ")\n \n");
if (goCommandCounter >= 5000) {
sb.append("GO \n \n");
goCommandCounter = 0;
}
goCommandCounter++;
}
}
sb.append("GO \n \n");
sb.append("--Time zones implementing Daylight savings \n \n");
for (String tzid : x) {
TimeZone localzone = TimeZone.getTimeZone(tzid);
if (localzone.useDaylightTime()) {
// region specific timezone
//TimeZone localzone = TimeZone.getTimeZone("Australia/Melbourne");
Date mintime, maxtime = null;
int offset = 0;
int offset1 = 0;
c1.setTimeInMillis(starttime.getTimeInMillis());
tempcal.setTimeInMillis(starttime.getTimeInMillis());
mintime = c1.getTime();
while (endtime.getTime().after(c1.getTime())) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.DATE, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
// take the time one day behind to loop hrs
c1.add(Calendar.DATE, -1);
//tempcal.add(Calendar.DATE, -1);
tempcal.setTimeInMillis(c1.getTimeInMillis());
// Now loop into hrs for further precision
for (int b = 0; b < 25; b++) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.HOUR, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
// take the time one hr behind to loop minutes
c1.add(Calendar.HOUR, -1);
tempcal.setTimeInMillis(c1.getTimeInMillis());
//tempcal.add(Calendar.HOUR, -1);
// now loop further for minutes
for (int c = 0; c < 60; c++) {
offset = localzone.getOffset(c1.getTimeInMillis());
c1.add(Calendar.MINUTE, 1);
offset1 = localzone.getOffset(c1.getTimeInMillis());
if (offset != offset1) {
maxtime = tempcal.getTime();
sb.append("INSERT timezone_offsets
(time_zone_id,start_time_gmt,
end_time_gmt,offset) \n");
sb.append("VALUES('" + localzone.getID()
+ "','" + dfgmt.format(mintime)
+ "','" + dfgmt59.format(maxtime)
+ "'," + localzone.getOffset
(tempcal.getTimeInMillis()) + ")\n \n");
if (goCommandCounter >= 5000) {
sb.append("GO \n\n");
goCommandCounter = 0;
}
goCommandCounter++;
mintime = c1.getTime();
}
tempcal.add(Calendar.MINUTE, 1);
}
}
tempcal.add(Calendar.HOUR, 1);
}
}
tempcal.add(Calendar.DATE, 1);
}
}
}
}
我首先使用一个string缓冲区来保存string数据，然后开始声明两个Timezone对象和两个DateFormat对象。一个timezone对象保持 GMT，另一个在循环过程中不断变化。java.util.Calendar是遍历从startyear到endyear的每一天的对象。您可能会注意到有两个循环，第一个是针对不实现夏令时的时区，第二个是针对有夏令时的时区。函数中的第二个循环很有趣。在这里，我将calendar对象逐日添加，直到找到给定时间区的偏移量差异。一旦找到差异，我将进入一个内部循环，遍历一天中的 24 小时，以找出偏移量在哪个小时发生变化。一旦找到小时，我将进一步深入另一个内部循环，找出偏移量在哪个分钟发生变化。我进一步假设偏移量在 59 秒时发生变化。我们无需遍历秒。您可以看到我使用了dfgmt59 SimpleDateFormat实例强制格式化输出中的 59 秒。
您可能需要不经常使用 jar 来重新生成 SQL 脚本，并且我添加的脚本。除非新版本的 JDK 支持新的时区，或者您需要生成比默认值更少年份的数据，否则timezone_offsets.sql应该足够了。
历史
- 2009 年 3 月 18 日：初始发布