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

Commit475c145

Browse files
committed
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 differentthan DB.The buggy implementation of setTimestamp() in PreparedStatement simplyused the toString() method of the java.sql.Timestamp object to convertto a string to send to the database. The format of this is yyyy-MM-ddhh:mm:ss.SSS which doesn't include any timezone information. Thereforethe DB assumes its timezone since none is specified. That is OK if thetimezone of the client and server are the same, however if they aredifferent the wrong timestamp is received by the server. For example ifthe client is running in timezone GMT and wants to send the timestampfor noon to a server running in PST (GMT-8 hours), then the server willreceive 2000-01-12 12:00:00.0 and interprete it as 2000-01-1212:00:00-08 which is 2000-01-12 04:00:00 in GMT. The fix is to send aformat to the server that includes the timezone offset. For simplicitysake the fix uses a SimpleDateFormat object with its timezone set to GMTso that '+00' can be used as the timezone for postgresql. This is doneas SimpleDateFormat doesn't support formating timezones in the waypostgresql expects.Bug#2) Incorrect handling of partial seconds in getting timestamps fromthe DBWhen the SimpleDateFormat object parses a string with a format likeyyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be threedecimal places (time precision in java is miliseconds = three decimalplaces). This seems like a bug in java to me, but it is unlikely to befixed anytime soon, so the postgresql code needed modification tosupport the java behaviour. So for example a string of '2000-01-1212:00:00.12-08' coming from the database was being converted to atimestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00. Thefix was to check for a '.' in the string and if one is found append onan extra zero to the fractional seconds part.Bug#3) Performance problemsIn fixing the above two bugs, I noticed some things that could beimproved. In PreparedStatement.setTimestamp(),PreparedStatement.setDate(), ResultSet.getTimestamp(), andResultSet.getDate() these methods were creating a new SimpleDateFormatobject everytime they were called. To avoid this unnecessary objectcreation overhead, I changed the code to use static variables forkeeping a single instance of the needed formating objects.Also the code used the + operator for string concatenation. As everyoneshould know this is very inefficient and the use of StringBuffers isprefered.I also did some cleanup in ResultSet.getTimestamp(). This method hashad multiple patches applied some of which resulted in code that was nolonger needed. For example the ISO timestamp format that postgresqluses specifies the timezone as an offset like '-08'. Code was added atone point to convert the postgresql format to the java one which isGMT-08:00, however the old code was left around which did nothing. Sothere was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz andyyyy-MM-dd hh:mm:sszzz. This second format would never be encounteredbecause zzz (i.e. -08) would be converted into the former (also notethat the SimpleDateFormat object treats zzzzzzzzz and zzz the same, thenumber of z's does not matter).There was another problem/fix mentioned on the email lists today bymcannon@internet.com which is also fixed by this patch:Bug#4) Fractional seconds lost when getting timestamp from the DBA patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzzbut 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 parent20dfd50 commit475c145

File tree

4 files changed

+117
-57
lines changed

4 files changed

+117
-57
lines changed

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -310,12 +310,11 @@ public void setBytes(int parameterIndex, byte x[]) throws SQLException
310310
* @param x the parameter value
311311
* @exception SQLException if a database access error occurs
312312
*/
313+
privatestaticfinalSimpleDateFormatDF1 =newSimpleDateFormat("yyyy-MM-dd");
313314
publicvoidsetDate(intparameterIndex,java.sql.Datex)throwsSQLException
314315
{
315-
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
316-
317-
set(parameterIndex,df.format(x));
318-
316+
set(parameterIndex,DF1.format(x));
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
@@ -349,9 +348,17 @@ public void setTime(int parameterIndex, Time x) throws SQLException
349348
* @param x the parameter value
350349
* @exception SQLException if a database access error occurs
351350
*/
351+
privatestaticSimpleDateFormatDF2 =getDF2();
352+
privatestaticSimpleDateFormatgetDF2() {
353+
SimpleDateFormatsdf =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
354+
sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
355+
returnsdf;
356+
}
352357
publicvoidsetTimestamp(intparameterIndex,Timestampx)throwsSQLException
353-
{
354-
set(parameterIndex,"'" +x.toString() +"'");
358+
{
359+
StringBufferstrBuf =newStringBuffer("'");
360+
strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
361+
set(parameterIndex,strBuf.toString());
355362
}
356363

357364
/**

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

Lines changed: 46 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -412,9 +412,8 @@ public java.sql.Date getDate(int columnIndex) throws SQLException
412412
Strings =getString(columnIndex);
413413
if(s==null)
414414
returnnull;
415-
SimpleDateFormatdf =newSimpleDateFormat("yyyy-MM-dd");
416415
try {
417-
returnnewjava.sql.Date(df.parse(s).getTime());
416+
returnnewjava.sql.Date(DF5.parse(s).getTime());
418417
}catch (ParseExceptione) {
419418
thrownewPSQLException("postgresql.res.baddate",newInteger(e.getErrorOffset()),s);
420419
}
@@ -457,30 +456,59 @@ public Time getTime(int columnIndex) throws SQLException
457456
* @return the column value; null if SQL NULL
458457
* @exception SQLException if a database access error occurs
459458
*/
459+
privatestaticfinalSimpleDateFormatDF1 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
460+
privatestaticfinalSimpleDateFormatDF2 =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
461+
privatestaticfinalSimpleDateFormatDF3 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
462+
privatestaticfinalSimpleDateFormatDF4 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
463+
privatestaticfinalSimpleDateFormatDF5 =newSimpleDateFormat("yyyy-MM-dd");
460464
publicTimestampgetTimestamp(intcolumnIndex)throwsSQLException
461465
{
462466
Strings =getString(columnIndex);
463467
if(s==null)
464468
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:
469+
470+
booleansubsecond;
471+
//if string contains a '.' we have fractional seconds
472+
if (s.indexOf('.') == -1) {
473+
subsecond =false;
474+
}else {
475+
subsecond =true;
476+
}
477+
478+
//here we are modifying the string from ISO format to a format java can understand
479+
//java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
480+
//and java expects three digits if fractional seconds are present instead of two for postgres
481+
//so this code strips off timezone info and adds on the GMT+/-...
482+
//as well as adds a third digit for partial seconds if necessary
483+
StringBufferstrBuf =newStringBuffer(s);
484+
charsub =strBuf.charAt(strBuf.length()-3);
485+
if (sub =='+' ||sub =='-') {
486+
strBuf.setLength(strBuf.length()-3);
487+
if (subsecond) {
488+
strBuf =strBuf.append('0').append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
489+
}else {
490+
strBuf =strBuf.append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
491+
}
492+
}elseif (subsecond) {
493+
strBuf =strBuf.append('0');
494+
}
495+
496+
s =strBuf.toString();
497+
471498
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");
499+
500+
if (s.length()>23 &&subsecond) {
501+
df =DF1;
502+
}elseif (s.length()>23 && !subsecond) {
503+
df =DF2;
504+
}elseif (s.length()>10 &&subsecond) {
505+
df =DF3;
506+
}elseif (s.length()>10 && !subsecond) {
507+
df =DF4;
480508
}else {
481-
df =newSimpleDateFormat("yyyy-MM-dd");
509+
df =DF5;
482510
}
483-
511+
484512
try {
485513
returnnewTimestamp(df.parse(s).getTime());
486514
}catch(ParseExceptione) {

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -310,12 +310,11 @@ public void setBytes(int parameterIndex, byte x[]) throws SQLException
310310
* @param x the parameter value
311311
* @exception SQLException if a database access error occurs
312312
*/
313+
privatestaticfinalSimpleDateFormatDF1 =newSimpleDateFormat("yyyy-MM-dd");
313314
publicvoidsetDate(intparameterIndex,java.sql.Datex)throwsSQLException
314315
{
315-
SimpleDateFormatdf =newSimpleDateFormat("''yyyy-MM-dd''");
316-
317-
set(parameterIndex,df.format(x));
318-
316+
set(parameterIndex,DF1.format(x));
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
@@ -349,9 +348,17 @@ public void setTime(int parameterIndex, Time x) throws SQLException
349348
* @param x the parameter value
350349
* @exception SQLException if a database access error occurs
351350
*/
351+
privatestaticSimpleDateFormatDF2 =getDF2();
352+
privatestaticSimpleDateFormatgetDF2() {
353+
SimpleDateFormatsdf =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
354+
sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
355+
returnsdf;
356+
}
352357
publicvoidsetTimestamp(intparameterIndex,Timestampx)throwsSQLException
353-
{
354-
set(parameterIndex,"'" +x.toString() +"'");
358+
{
359+
StringBufferstrBuf =newStringBuffer("'");
360+
strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
361+
set(parameterIndex,strBuf.toString());
355362
}
356363

357364
/**

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

Lines changed: 45 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -415,9 +415,8 @@ public java.sql.Date getDate(int columnIndex) throws SQLException
415415
Strings =getString(columnIndex);
416416
if(s==null)
417417
returnnull;
418-
SimpleDateFormatdf =newSimpleDateFormat("yyyy-MM-dd");
419418
try {
420-
returnnewjava.sql.Date(df.parse(s).getTime());
419+
returnnewjava.sql.Date(DF5.parse(s).getTime());
421420
}catch (ParseExceptione) {
422421
thrownewPSQLException("postgresql.res.baddate",newInteger(e.getErrorOffset()),s);
423422
}
@@ -460,47 +459,66 @@ public Time getTime(int columnIndex) throws SQLException
460459
* @return the column value; null if SQL NULL
461460
* @exception SQLException if a database access error occurs
462461
*/
462+
privatestaticfinalSimpleDateFormatDF1 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
463+
privatestaticfinalSimpleDateFormatDF2 =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
464+
privatestaticfinalSimpleDateFormatDF3 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
465+
privatestaticfinalSimpleDateFormatDF4 =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss");
466+
privatestaticfinalSimpleDateFormatDF5 =newSimpleDateFormat("yyyy-MM-dd");
463467
publicTimestampgetTimestamp(intcolumnIndex)throwsSQLException
464468
{
465469
Strings =getString(columnIndex);
466470
if(s==null)
467471
returnnull;
468-
469-
// This works, but it's commented out because Michael Stephenson's
470-
// solution is better still:
471-
//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
472-
// Modification by Jan Thomae
473-
Stringsub =s.substring(s.length() -3,s.length()-2);
474-
if (sub.equals("+") ||sub.equals("-")) {
475-
s =s.substring(0,s.length()-3) +"GMT"+s.substring(s.length()-3,s.length())+":00";
472+
473+
booleansubsecond;
474+
//if string contains a '.' we have fractional seconds
475+
if (s.indexOf('.') == -1) {
476+
subsecond =false;
477+
}else {
478+
subsecond =true;
479+
}
480+
481+
//here we are modifying the string from ISO format to a format java can understand
482+
//java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format
483+
//and java expects three digits if fractional seconds are present instead of two for postgres
484+
//so this code strips off timezone info and adds on the GMT+/-...
485+
//as well as adds a third digit for partial seconds if necessary
486+
StringBufferstrBuf =newStringBuffer(s);
487+
charsub =strBuf.charAt(strBuf.length()-3);
488+
if (sub =='+' ||sub =='-') {
489+
strBuf.setLength(strBuf.length()-3);
490+
if (subsecond) {
491+
strBuf =strBuf.append('0').append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
492+
}else {
493+
strBuf =strBuf.append("GMT").append(s.substring(s.length()-3,s.length())).append(":00");
494+
}
495+
}elseif (subsecond) {
496+
strBuf =strBuf.append('0');
476497
}
477-
// -------
478-
// Michael Stephenson's solution:
498+
499+
s =strBuf.toString();
500+
479501
SimpleDateFormatdf =null;
480502

481-
// Modification by Jan Thomae
482-
if (s.length()>27) {
483-
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
484-
}else
485-
// -------
486-
if (s.length()>21 &&s.indexOf('.') != -1) {
487-
df =newSimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
488-
}elseif (s.length()>19 &&s.indexOf('.') == -1) {
489-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
490-
}elseif (s.length()>19 &&s.indexOf('.') != -1) {
491-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
492-
}elseif (s.length()>10 &&s.length()<=18) {
493-
df =newSimpleDateFormat("yyyy-MM-dd HH:MM:ss");
503+
if (s.length()>23 &&subsecond) {
504+
df =DF1;
505+
}elseif (s.length()>23 && !subsecond) {
506+
df =DF2;
507+
}elseif (s.length()>10 &&subsecond) {
508+
df =DF3;
509+
}elseif (s.length()>10 && !subsecond) {
510+
df =DF4;
494511
}else {
495-
df =newSimpleDateFormat("yyyy-MM-dd");
512+
df =DF5;
496513
}
497-
514+
498515
try {
499516
returnnewTimestamp(df.parse(s).getTime());
500517
}catch(ParseExceptione) {
501518
thrownewPSQLException("postgresql.res.badtimestamp",newInteger(e.getErrorOffset()),s);
502519
}
503520
}
521+
504522

505523
/**
506524
* A column value can be retrieved as a stream of ASCII characters

0 commit comments

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