Thread

1. Re: Re: timestamps cannot be created without time zones

   Rainer Mager <rmager@vgkk.com> — 2001-08-31T06:39:42Z

   Hi all,
   
   	Sorry to reopen this issue but I still think there is a bug somewhere,
   perhaps in the JDBC driver. The code and the end of this message
   demonstrates the bug. Basically I write a timestamp to the database and then
   read it back and  what I write and what I get back are different. I don't
   see how I can progrmatically make this correct in a consistent way without
   knowing the "magic" dates in Postgres. Note that I believe there are more
   than just one magic date. Apparently at ever older date (around 10,000 BC I
   believe) the seconds are dropped.
   	The output from the code is (the computer's time was 03:23:49):
   
   1850-Jan-01 03:23:49 JST
   1850-Jan-01 06:23:49 JST
   
   Thanks,
   
   --Rainer
   
   
   SimpleDateFormat format = new SimpleDateFormat( "yyyy-MMM-dd hh:mm:ss zz" );
   Calendar cal = new GregorianCalendar();
   cal.set( 1850, 00, 01 );
   java.util.Date date = cal.getTime();
   System.out.println( format.format( date ) );
   try {
   	PreparedStatement ps = con.prepareStatement(
   		"update cust_prop_date set value = ? where customer_id = 8791"
   	);
   	ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
   	ps.execute();
   	ps.close();
   
   	ps = con.prepareStatement( "select value from cust_prop_date where
   customer_id=8791" );
   	ResultSet rs = ps.executeQuery();
   	rs.next();
   	date = new java.util.Date( rs.getTimestamp( "value" ).getTime() );
   	rs.close();
   	ps.close();
   } catch( Exception e ) {
   }
   System.out.println( format.format( date ) );
   
   
   

2. Re: Re: timestamps cannot be created without time zones

   Rainer Mager <rmager@vgkk.com> — 2001-09-13T22:42:28Z

   I posted this about 2 weeks ago and saw no further follow ups. Is this
   timestamp thing not considered a bug? Or am I just doing something wrong?
   
   Thanks,
   
   --Rainer
   
   > -----Original Message-----
   > 	Sorry to reopen this issue but I still think there is a bug
   > somewhere,
   > perhaps in the JDBC driver. The code and the end of this message
   > demonstrates the bug. Basically I write a timestamp to the
   > database and then
   > read it back and  what I write and what I get back are different. I don't
   > see how I can progrmatically make this correct in a consistent way without
   > knowing the "magic" dates in Postgres. Note that I believe there are more
   > than just one magic date. Apparently at ever older date (around
   > 10,000 BC I
   > believe) the seconds are dropped.
   > 	The output from the code is (the computer's time was 03:23:49):
   >
   > 1850-Jan-01 03:23:49 JST
   > 1850-Jan-01 06:23:49 JST
   >
   > Thanks,
   >
   > --Rainer
   >
   >
   > SimpleDateFormat format = new SimpleDateFormat( "yyyy-MMM-dd
   > hh:mm:ss zz" );
   > Calendar cal = new GregorianCalendar();
   > cal.set( 1850, 00, 01 );
   > java.util.Date date = cal.getTime();
   > System.out.println( format.format( date ) );
   > try {
   > 	PreparedStatement ps = con.prepareStatement(
   > 		"update cust_prop_date set value = ? where
   > customer_id = 8791"
   > 	);
   > 	ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
   > 	ps.execute();
   > 	ps.close();
   >
   > 	ps = con.prepareStatement( "select value from cust_prop_date where
   > customer_id=8791" );
   > 	ResultSet rs = ps.executeQuery();
   > 	rs.next();
   > 	date = new java.util.Date( rs.getTimestamp( "value" ).getTime() );
   > 	rs.close();
   > 	ps.close();
   > } catch( Exception e ) {
   > }
   > System.out.println( format.format( date ) );
   
   
   

3. Re: Re: timestamps cannot be created without time zones

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-14T14:41:23Z

   "Rainer Mager" <rmager@vgkk.com> writes:
   >> Apparently at ever older date (around
   >> 10,000 BC I
   >> believe) the seconds are dropped.
   
   You do realize that timestamps are floating point seconds relative to AD
   2000, and so the accuracy decreases as you get further away from current
   time?
   
   >> The output from the code is (the computer's time was 03:23:49):
   >> 
   >> 1850-Jan-01 03:23:49 JST
   >> 1850-Jan-01 06:23:49 JST
   
   I don't believe that Postgres will associate any timezone at all with
   timestamps outside the range of the underlying OS' timezone database.
   I get just
   
   regression=# select '1850-Jan-01 03:23:49'::timestamp;
         ?column?       
   ---------------------
    1850-01-01 03:23:49
   (1 row)
   
   I'd say the problem here is on the Java side: something on the client
   side is inappropriately attaching a timezone to a value that should not
   have one.  Possibly you should take this up on pgsql-jdbc; or perhaps
   it's a problem with the Java datetime datatypes you are using.
   
   			regards, tom lane