SQL Server和Java之间的时间戳差异 [英] Timestamp discrepancies between SQL server and Java
问题描述
我需要将一个简单的过程从Java代码复制到SQL Server存储过程.它将进入生产中的SQL Azure数据库,但是我正在针对本地SQL Express 12安装对其进行测试.
I need to replicate a simple procedure from Java code to SQL Server stored procedure. It will go into a SQL Azure db in production, but I'm testing it against my local SQL Express 12 install.
此存储过程的一部分是将一些值连接到字符串中.
A part of this stored procedure is to concatenate some values into a string.
这是我的示例Java代码:
This is my example Java code:
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import com.google.common.base.Strings;
public static String concat() {
//init variables with sample data
DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss.SSS");
Timestamp date = new Timestamp(dateFormat.parse("04/04/2014 21:07:13.897").getTime());
//format variables into 0-filled strings
String formattedDate = String.format("%011d", date.getTime() / 1000);
//concat those strings
String finalString = ... + formattedDate + ...;
return finalString;
}
变量:
| date | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396638433 |
这在SQL中是等效的:
This is the equivalent in SQL:
DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);
--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';
--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date), '00000000000');
--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);
变量:
| date | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396645633 |
在检查输出是否相同时,我注意到日期不相同:
While checking if the output was the same I noticed the dates are not the same:
Java output: 01396638433
MSSQL output: 01396645633
我打开了本网站,以了解这种差异是什么意思:
I opened this site to see what this difference meant:
Java: GMT: Fri, 04 Apr 2014 19:07:13 GMT, Your time zone: 4/4/2014 21:07:13 GMT+2
MSSQL: GMT: Fri, 04 Apr 2014 21:07:13 GMT, Your time zone: 4/4/2014 23:07:13 GMT+2
恰好相差两个小时.
我发现要针对SQL Server运行查询以检查时区设置:
I've found a query to run against SQL Server to check time zone settings:
DECLARE @TZ SMALLINT
SELECT @TZ=DATEPART(TZ, SYSDATETIMEOFFSET())
DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone, CAST(@TZ/60 AS VARCHAR(5))+':'+Cast(ABS(@TZ)%60 AS VARCHAR(5));
输出:
| Time zone | Offset |
| ----------------------- | ------- |
| W. Europe Standard Time | 2:0 |
我这样检查了JVM时区:
I checked JVM time zone like this:
Calendar now = Calendar.getInstance();
System.out.println(now.getTimeZone());
System.out.println(System.getProperties().get("user.timezone").toString());
输出:
sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000, dstSavings=3600000,
transitions=143, lastRule=java.util.SimpleTimeZone[id=Europe/Berlin, offset=3600000,
dstSavings=3600000, startYear=0, startMode=2, startMonth=2, startDay=-1,
startDayOfWeek=1, startTime=3600000, startTimeMode=2, endMode=2, endMonth=9,
endDay=-1, endDayOfWeek=1, endTime=3600000, endTimeMode=2]]
Europe/Berlin
如何在Java和SQL Server之间获得相等的时间戳?
How can I get equal timestamps between Java and SQL Server?
推荐答案
即使Mark Rotteveel和dean给出了启发性的答案,我还是做了以下事情:
Even though Mark Rotteveel and dean gave enlightening answers I ended up doing the following:
在我设置的应用程序初始化方法的开始
at the beginning of my application init method I set
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
以及所有对SQL的调用
and all SQL calls to
getdate()
已替换为
getutcdate()
谢谢您的时间!
这篇关于SQL Server和Java之间的时间戳差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!