RE: jdbc with datetime field - unwanted timezone conversion

• Previous message: Carb Simien [MSFT]: "RE: connection pool problem using in Tomcat"
• In reply to: Carb Simien [MSFT]: "RE: jdbc with datetime field - unwanted timezone conversion"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 31 Aug 2004 16:39:03 -0700

Someone in our local user group has suggested what is going on and based on
the evidence, I think he's right (we just figured it out today). Essentially
the issue is this.

The date information is in a datetime field in a SQL Server 2000 database on
a different machine. As I read the docs on the datetime type, there is no
associated timezone. So, when the jdbc driver on my machine gets the value
from the remote database, it has to guess what the timezone is and makes the
assumption that the datetime field in the database server has the same
timezone as the server running the jdbc driver. Unfortunately, in our case,
that isn't true. I know that the value in the database is GMT. If I offset
the value received from the database by the number of milliseconds to GMT
based on the timezone on my machine (in my case, 6 hours from MDT to GMT),
then java shows the correct value.

Your code below is essentially what I did originally. It probably works
fine when you store the datetime from your machine to the remote server and
then it read it back or when all the machines are on the same timezone.
However, try writing the date from the database machine running GMT and then
it read it back on a different machine running a different timezone. Bet you
don't get the same time back.

Someone suggested I try using the getTimestamp() that has a second parameter
as a Calendar with the timezone of the datetime in the database (in my case,
I'd specify GMT). I haven't tried it yet, but the description in the
javadocs sounds promising. I do know that using a TimeZone object to get the
offset to GMT (don't forget to adjust for DST) and using Calendar.add() to
adjust the time seems to solve the problem though it feels kludgy.

Cheers

""Carb Simien [MSFT]"" wrote:

>
> --------------------
> | Thread-Topic: jdbc with datetime field - unwanted timezone conversion
> | thread-index: AcSMj5gnHbLf5bAYTqOU3MRZc9R5+Q==
> | X-WBNR-Posting-Host: 209.63.42.209
> | From: =?Utf-8?B?VmlydHVhbCBQcm9ncmFtbWVy?= <Virtual
> Programmer@discussions.microsoft.com>
> | Subject: jdbc with datetime field - unwanted timezone conversion
> | Date: Fri, 27 Aug 2004 16:43:03 -0700
> | Lines: 10
> | Message-ID: <32B0C910-3926-4060-A13F-4014058F720C@microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I have a database which contains records each of which has a datetime
> field.
> | The database machine is set up as GMT. I access this data from a
> different
> | machine which is running in a different timezone (EST for example). The
> | value returned by the database appears to be offset by some value based
> on
> | the local machine's timezone. I just want the raw GMT value regardless
> of
> | what timezone the machine running the jdbc driver is running. Any
> | suggestions how I do this?
> |
> | I'm using resultset.getTimestamp() and pushing that to a Calendar object
> if
> | that helps.
> |
>
> If you directly output the "resultset.getTimestamp()" before passing it to
> the Calendar object, what is the value that is returned? Can you post the
> code you are using to demonstrate this problem? If I change the Regional
> Settings on my remote SQL Server to use GMT and then insert a date value, I
> can query the information from a separate client machine (in a different
> time zone) without a problem.
>
> // create table datetest(col1 datetime)
> // go
> // insert datetest select getdate()
>
> Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
> Connection connection =
> DriverManager.getConnection("jdbc:microsoft:sqlserver://remoteSQL:1433;datab
> asename=jdbc", "sa", "password");
>
> // Query the database
> Statement stmt = connection.createStatement();
> ResultSet rs = null;
> rs = stmt.executeQuery("select col1 from datetest");
> while (rs.next())
> {
> Timestamp ts = rs.getTimestamp(1);
> System.out.println(ts);
> }
> rs.close();
> stmt.close();
> connection.close();
>
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
>
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
>

• Previous message: Carb Simien [MSFT]: "RE: connection pool problem using in Tomcat"
• In reply to: Carb Simien [MSFT]: "RE: jdbc with datetime field - unwanted timezone conversion"
• Messages sorted by: [ date ] [ thread ]