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

some timestamps are wrong from JDBC

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: some timestamps are wrong from JDBC
Date: 2001-07-17 22:14:42
Message-ID: 200107172214.f6HMEgI15218@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Rainer Mager (rmager(at)vgkk(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
some timestamps are wrong from JDBC

Long Description
Postgres 7.1 (or its driver) munges timestamps.  
When the milliseconds M are in the range:

0 < (M mod 1000) < 100

then the fractional part of the second is incorrectly
multiplied by 10.  The whole parts of the second and larger
are still correct.  

To reproduce this bug, please see the unit test code 
and database results below.  You can see the (sometimes incorrect)
timestamp in the database and the long that was used to create it.
The unit test code also fails, so this is not just a psql problem.
Note that Postgres claims a timestamp resolution to the microsecond, 
so I only tested milliseconds of even multiples of 10.

jdb=> select * from test_timestamp_table_9953612873 ;
            ts             | long
---------------------------+------
 1970-01-01 09:00:00.95+09 |  950
 1970-01-01 09:00:00.96+09 |  960
 1970-01-01 09:00:00.97+09 |  970
 1970-01-01 09:00:00.98+09 |  980
 1970-01-01 09:00:00.99+09 |  990
 1970-01-01 09:00:01+09    | 1000
 1970-01-01 09:00:01.10+09 | 1010
 1970-01-01 09:00:01.20+09 | 1020
 1970-01-01 09:00:01.30+09 | 1030
 1970-01-01 09:00:01.40+09 | 1040
 1970-01-01 09:00:01.50+09 | 1050
 1970-01-01 09:00:01.60+09 | 1060
 1970-01-01 09:00:01.70+09 | 1070
 1970-01-01 09:00:01.80+09 | 1080
 1970-01-01 09:00:01.90+09 | 1090
 1970-01-01 09:00:01.10+09 | 1100
 1970-01-01 09:00:01.11+09 | 1110
 1970-01-01 09:00:01.12+09 | 1120
 1970-01-01 09:00:01.13+09 | 1130
 1970-01-01 09:00:01.14+09 | 1140
 1970-01-01 09:00:01.15+09 | 1150
 1970-01-01 09:00:01.16+09 | 1160
 1970-01-01 09:00:01.17+09 | 1170
 1970-01-01 09:00:01.18+09 | 1180
 1970-01-01 09:00:01.19+09 | 1190
 1970-01-01 09:00:01.20+09 | 1200
 1970-01-01 09:00:01.21+09 | 1210
 1970-01-01 09:00:01.22+09 | 1220
 1970-01-01 09:00:01.23+09 | 1230
 1970-01-01 09:00:01.24+09 | 1240
 1970-01-01 09:00:01.25+09 | 1250
 1970-01-01 09:00:01.26+09 | 1260
 1970-01-01 09:00:01.27+09 | 1270
 1970-01-01 09:00:01.28+09 | 1280
 1970-01-01 09:00:01.29+09 | 1290
(35 rows)


Sample Code
/**
 * tests a timestamp being written to and from the database.
 */
public void testTimestampFromDatabase() throws Exception {
	final int TIMESTAMP_RESOLUTION = 10;	// in millis, i.e., 1 
microsec
	String tableName = "test_timestamp_table_" + System.currentTimeMillis
();
	Connection con = checkOutConnection();
	try {
		Statement st = con.createStatement();
		st.execute(
			"CREATE TABLE " + tableName
				+ " ( ts TIMESTAMP, long INT8 )"	// Postgres only?
		);
		Database.closeStatement( st );

		PreparedStatement ps = con.prepareStatement(
			"INSERT INTO " + tableName + " (ts, long) VALUES (?,?)"
		);
		long time = 950L;
		for( int i = 0; i < 35; i++ ) {
			ps.setTimestamp( 1, new Timestamp( time ) );
			ps.setLong( 2, time );
			ps.executeUpdate();
			time += TIMESTAMP_RESOLUTION;
		}
		Database.closeStatement( ps );

		st = con.createStatement();
		ResultSet rs = st.executeQuery(
			"SELECT ts, long FROM " + tableName + " ORDER BY ts DESC"
		);
		while( rs.next() ) {
			time -= TIMESTAMP_RESOLUTION;
			assertEquals(
				"timestamp",
				new Timestamp( time ),
				rs.getTimestamp( "ts" )
			);
			assertEquals(
				"reference long",
				new Timestamp( rs.getLong( "long" ) ),
				rs.getTimestamp( "ts" )
			);
		}
		Database.closeStatement( st );
	} finally {
		try {
			Statement st = con.createStatement();
			st.execute( "DROP TABLE " + tableName );
			Database.closeStatement( st );
		} finally {
			checkInConnection( con );
		}
	}
}

No file was uploaded with this report


pgsql-bugs by date

Next:From: Keith F IrwinDate: 2001-07-17 22:20:49
Subject: HELP! BUG? pg_dump mucks up grant/revoke
Previous:From: Geoff ReedyDate: 2001-07-17 21:45:53
Subject: bug in the money type

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