Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit26e5664

Browse files
committed
Attached is a revised patch that removes the static SimpleDateFormat
objects that Thomas pointed out might be a problem.PPS. I have included and updated the comments from the original patchrequest to reflect the changes made in this revised patch.> Attached is a set of patches for a couple of bugs dealing with> timestamps in JDBC.>> Bug#1) Incorrect timestamp stored in DB if client timezone different> than DB.> The buggy implementation of setTimestamp() in PreparedStatement simply> used the toString() method of the java.sql.Timestamp object to convert> to a string to send to the database. The format of this is yyyy-MM-dd> hh:mm:ss.SSS which doesn't include any timezone information. Therefore> the DB assumes its timezone since none is specified. That is OK if the> timezone of the client and server are the same, however if they are> different the wrong timestamp is received by the server. For example if> the client is running in timezone GMT and wants to send the timestamp> for noon to a server running in PST (GMT-8 hours), then the server will> receive 2000-01-12 12:00:00.0 and interprete it as 2000-01-12> 12:00:00-08 which is 2000-01-12 04:00:00 in GMT. The fix is to send a> format to the server that includes the timezone offset. For simplicity> sake the fix uses a SimpleDateFormat object with its timezone set to GMT> so that '+00' can be used as the timezone for postgresql. This is done> as SimpleDateFormat doesn't support formating timezones in the way> postgresql expects.>> Bug#2) Incorrect handling of partial seconds in getting timestamps from> the DB>> When the SimpleDateFormat object parses a string with a format like> yyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be three> decimal places (time precision in java is miliseconds = three decimal> places). This seems like a bug in java to me, but it is unlikely to be> fixed anytime soon, so the postgresql code needed modification to> support the java behaviour. So for example a string of '2000-01-12> 12:00:00.12-08' coming from the database was being converted to a> timestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00. The> fix was to check for a '.' in the string and if one is found append on> an extra zero to the fractional seconds part.>>> I also did some cleanup in ResultSet.getTimestamp(). This method has> had multiple patches applied some of which resulted in code that was no> longer needed. For example the ISO timestamp format that postgresql> uses specifies the timezone as an offset like '-08'. Code was added at> one point to convert the postgresql format to the java one which is> GMT-08:00, however the old code was left around which did nothing. So> there was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz and> yyyy-MM-dd hh:mm:sszzz. This second format would never be encountered> because zzz (i.e. -08) would be converted into the former (also note> that the SimpleDateFormat object treats zzzzzzzzz and zzz the same, the> number of z's does not matter).>>> There was another problem/fix mentioned on the email lists today by> mcannon@internet.com which is also fixed by this patch:>> Bug#3) Fractional seconds lost when getting timestamp from the DB> A patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzz> but not the fractional seconds version yyyy-MM-dd hh:mm:ss.SSzzzzzzzzz.> The code is fixed to handle this case as well.Barry Lind
1 parent7b9dc71 commit26e5664

File tree

4 files changed

+95
-50
lines changed

4 files changed

+95
-50
lines changed

‎src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -312,10 +312,9 @@ public void setBytes(int parameterIndex, byte x[]) throws SQLException
312312
*/
313313
publicvoidsetDate(intparameterIndex,java.sql.Datex)throwsSQLException
314314
{
315-
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
316-
315+
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
317316
set(parameterIndex,df.format(x));
318-
317+
319318
// The above is how the date should be handled.
320319
//
321320
// However, in JDK's prior to 1.1.6 (confirmed with the
@@ -350,8 +349,12 @@ public void setTime(int parameterIndex, Time x) throws SQLException
350349
* @exception SQLException if a database access error occurs
351350
*/
352351
publicvoidsetTimestamp(intparameterIndex,Timestampx)throwsSQLException
353-
{
354-
set(parameterIndex,"'" +x.toString() +"'");
352+
{
353+
SimpleDateFormatdf =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
354+
df.setTimeZone(TimeZone.getTimeZone("GMT"));
355+
StringBufferstrBuf =newStringBuffer("'");
356+
strBuf.append(df.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
357+
set(parameterIndex,strBuf.toString());
355358
}
356359

357360
/**

‎src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java

Lines changed: 40 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -462,25 +462,49 @@ public Timestamp getTimestamp(int columnIndex) throws SQLException
462462
Strings =getString(columnIndex);
463463
if(s==null)
464464
returnnull;
465-
466-
// This works, but it's commented out because Michael Stephenson's
467-
// solution is better still:
468-
//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
469-
470-
// Michael Stephenson's solution:
465+
466+
booleansubsecond;
467+
//if string contains a '.' we have fractional seconds
468+
if (s.indexOf('.') == -1) {
469+
subsecond =false;
470+
}else {
471+
subsecond =true;
472+
}
473+
474+
//here we are modifying the string from ISO format to a format java can understand
475+
//java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
476+
//and java expects three digits if fractional seconds are present instead of two for postgres
477+
//so this code strips off timezone info and adds on the GMT+/-...
478+
//as well as adds a third digit for partial seconds if necessary
479+
StringBufferstrBuf =newStringBuffer(s);
480+
charsub =strBuf.charAt(strBuf.length()-3);
481+
if (sub =='+' ||sub =='-') {
482+
strBuf.setLength(strBuf.length()-3);
483+
if (subsecond) {
484+
strBuf =strBuf.append('0').append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
485+
}else {
486+
strBuf =strBuf.append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
487+
}
488+
}elseif (subsecond) {
489+
strBuf =strBuf.append('0');
490+
}
491+
492+
s =strBuf.toString();
493+
471494
SimpleDateFormatdf =null;
472-
if (s.length()>21 &&s.indexOf('.') != -1) {
473-
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
474-
}elseif (s.length()>19 &&s.indexOf('.') == -1) {
475-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
476-
}elseif (s.length()>19 &&s.indexOf('.') != -1) {
477-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
478-
}elseif (s.length()>10 &&s.length()<=18) {
479-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss");
495+
496+
if (s.length()>23 &&subsecond) {
497+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
498+
}elseif (s.length()>23 && !subsecond) {
499+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
500+
}elseif (s.length()>10 &&subsecond) {
501+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
502+
}elseif (s.length()>10 && !subsecond) {
503+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
480504
}else {
481-
df =newSimpleDateFormat("yyyy-MM-dd");
505+
df =newSimpleDateFormat("yyyy-MM-dd");
482506
}
483-
507+
484508
try {
485509
returnnewTimestamp(df.parse(s).getTime());
486510
}catch(ParseExceptione) {

‎src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -312,10 +312,10 @@ public void setBytes(int parameterIndex, byte x[]) throws SQLException
312312
*/
313313
publicvoidsetDate(intparameterIndex,java.sql.Datex)throwsSQLException
314314
{
315-
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
316-
315+
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
316+
317317
set(parameterIndex,df.format(x));
318-
318+
319319
// The above is how the date should be handled.
320320
//
321321
// However, in JDK's prior to 1.1.6 (confirmed with the
@@ -350,8 +350,12 @@ public void setTime(int parameterIndex, Time x) throws SQLException
350350
* @exception SQLException if a database access error occurs
351351
*/
352352
publicvoidsetTimestamp(intparameterIndex,Timestampx)throwsSQLException
353-
{
354-
set(parameterIndex,"'" +x.toString() +"'");
353+
{
354+
SimpleDateFormatdf =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
355+
df.setTimeZone(TimeZone.getTimeZone("GMT"));
356+
StringBufferstrBuf =newStringBuffer("'");
357+
strBuf.append(df.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
358+
set(parameterIndex,strBuf.toString());
355359
}
356360

357361
/**

‎src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java

Lines changed: 38 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -468,33 +468,46 @@ public Timestamp getTimestamp(int columnIndex) throws SQLException
468468
if(s==null)
469469
returnnull;
470470

471-
// This works, but it's commented out because Michael Stephenson's
472-
// solution is better still:
473-
//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
474-
// Modification by Jan Thomae
475-
Stringsub =s.substring(s.length() -3,s.length()-2);
476-
if (sub.equals("+") ||sub.equals("-")) {
477-
s =s.substring(0,s.length()-3) +"GMT"+s.substring(s.length()-3,s.length())+":00";
478-
}
479-
// -------
480-
// Michael Stephenson's solution:
471+
booleansubsecond;
472+
//if string contains a '.' we have fractional seconds
473+
if (s.indexOf('.') == -1) {
474+
subsecond =false;
475+
}else {
476+
subsecond =true;
477+
}
478+
479+
//here we are modifying the string from ISO format to a format java can understand
480+
//java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
481+
//and java expects three digits if fractional seconds are present instead of two for postgres
482+
//so this code strips off timezone info and adds on the GMT+/-...
483+
//as well as adds a third digit for partial seconds if necessary
484+
StringBufferstrBuf =newStringBuffer(s);
485+
charsub =strBuf.charAt(strBuf.length()-3);
486+
if (sub =='+' ||sub =='-') {
487+
strBuf.setLength(strBuf.length()-3);
488+
if (subsecond) {
489+
strBuf =strBuf.append('0').append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
490+
}else {
491+
strBuf =strBuf.append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
492+
}
493+
}elseif (subsecond) {
494+
strBuf =strBuf.append('0');
495+
}
496+
497+
s =strBuf.toString();
498+
481499
SimpleDateFormatdf =null;
482500

483-
// Modification by Jan Thomae
484-
if (s.length()>27) {
485-
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
486-
}else
487-
// -------
488-
if (s.length()>21 &&s.indexOf('.') != -1) {
489-
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
490-
}elseif (s.length()>19 &&s.indexOf('.') == -1) {
491-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
492-
}elseif (s.length()>19 &&s.indexOf('.') != -1) {
493-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
494-
}elseif (s.length()>10 &&s.length()<=18) {
495-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss");
501+
if (s.length()>23 &&subsecond) {
502+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
503+
}elseif (s.length()>23 && !subsecond) {
504+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
505+
}elseif (s.length()>10 &&subsecond) {
506+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
507+
}elseif (s.length()>10 && !subsecond) {
508+
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
496509
}else {
497-
df =newSimpleDateFormat("yyyy-MM-dd");
510+
df =newSimpleDateFormat("yyyy-MM-dd");
498511
}
499512

500513
try {
@@ -504,6 +517,7 @@ public Timestamp getTimestamp(int columnIndex) throws SQLException
504517
}
505518
}
506519

520+
507521
/**
508522
* A column value can be retrieved as a stream of ASCII characters
509523
* and then read in chunks from the stream. This method is

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp