Timestamp discrepancies between SQL server and Java

Question

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?

Answer 1

On SQL Server side of things --

There is a function in SQL Server called getutcdate() which returns current UTC time. Comparing this with getdate() you can get the time difference from UTC and modify the value to format.

select datediff(s, getutcdate(), getdate())

Better than accessing registry in any case.

So, the SQL Server code should look like this:

DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);
DECLARE @diff_sec int;

--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';

--get the difference between UTC and local in seconds
SET @diff_sec = datediff(s, getutcdate(), getdate());

--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date) - @diff_sec, '00000000000');

--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);

Answer 2

JDBC requires - absent any timezone information - that timestamps are stored and retrieved using the local timezone.

This means that if your local system is Europe\Berlin, then a date stored as 2014-04-04 21:07:13.897 in the database is processed as 2014-04-04 21:07:13.897 CEST (Central European Summer Time) and not as 2014-04-04 21:07:13.897 UTC.

CEST offset is 2 hours ahead of UTC or 7200 seconds, which explains the difference you observe as:

1396645633 - 1396645633 = 7200

Similar when Java stores a timestamp into a database, it will send the timestamp as if it is in the current timezone. So if you try to store 2014-04-04 21:07:13.897 UTC, then it uses 2014-04-04 23:07:13.897 CEST and it sends to SQL Server 2014-04-04 23:07:13.897.

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

See for more details: Is java.sql.Timestamp timezone specific?

Answer 3

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!