Skip site navigation (1) Skip section navigation (2)

pgsql/src/interfaces/jdbc/org/postgresql jdbc1 ...

From: Bruce Momjian - CVS <momjian(at)hub(dot)org>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql/src/interfaces/jdbc/org/postgresql jdbc1 ...
Date: 2001-01-13 05:18:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-committers
CVSROOT:	/home/projects/pgsql/cvsroot
Module name:	pgsql
Changes by:	momjian(at)hub(dot)org	01/01/13 00:18:05

Modified files:

Log message:
	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.
	Bug#3) Performance problems
	In fixing the above two bugs, I noticed some things that could be
	improved.  In PreparedStatement.setTimestamp(),
	PreparedStatement.setDate(), ResultSet.getTimestamp(), and
	ResultSet.getDate() these methods were creating a new SimpleDateFormat
	object everytime they were called.  To avoid this unnecessary object
	creation overhead, I changed the code to use static variables for
	keeping a single instance of the needed formating objects.
	Also the code used the + operator for string concatenation.  As everyone
	should know this is very inefficient and the use of StringBuffers is
	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(at)internet(dot)com which is also fixed by this patch:
	Bug#4) 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

pgsql-committers by date

Next:From: Peter Eisentraut - PostgreSQLDate: 2001-01-13 18:34:51
Subject: pgsql/doc/src/sgml datatype.sgml func.sgml syn ...
Previous:From: Bruce Momjian - CVSDate: 2001-01-13 05:09:35
Subject: [WEBMASTER] 'www/html/docs faq-dev-english.html faq-english.html'

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group