java和sql server,SQL Server和Java之间的时间戳差异

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.

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;

}

Variables:

| date | formatted_date |

| ----------------------- | -------------- |

| 2014-04-04 21:07:13.897 | 01396638433 |

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, ...);

Variables:

| 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

Exactly two hours difference.

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));

Output:

| Time zone | Offset |

| ----------------------- | ------- |

| W. Europe Standard Time | 2:0 |

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());

Output:

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

How can I get equal timestamps between Java and SQL Server?

解决方案

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"));

and all SQL calls to

getdate()

have been replaced with

getutcdate()

Thank you for your time!