Rainer Mager ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
