Re: help with preparedStatement.setTimestamp with Calendar

Shankar Unni <shankarunni <at> netscape.net> writes:

> 
> Dan Jatnieks wrote:
> 
> > e.g. I have 2006-01-01 00:00:00 (UTC) as the timestamp value and expect that
> > when it is written to the database it will become 2005-12-31 19:00:00 (EST).
> 
> So far, so good.
> 
> > Calendar utc = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
> > Calendar cal = Calendar.getInstance("EST");
> > PreparedStatement stmt = m_conn.prepareStatement(sql);
> > 
> > ...
> > 
> > // Use the UTC Calendar to create a new timestamp and the target
> > // Calendar to adjust it to the correct timezone and set the
> > // timestamp value.
> > Timestamp tstamp = new Timestamp(utc.getTimeInMillis());
> > stmt.setTimestamp(2, tstamp, cal);
> 
> But this isn't doing anything (at least, I don't see how you plan on 
> getting the time "00:00:00" from this. How do you set the time in the 
> calendar called "utc"? By default, a newly-created calendar contains the 
> current date and time.
> 
> Did you do
> 
>    utc.set(Calendar.HOUR_OF_DAY, 0);
>    ...
> 
> etc., to set the time in "utc", before you called utc.getTimeInMillis()?
>

Yes. Here's a more complete testcase:

public void mySQLTest() throws SQLException, IllegalArgumentException {
	Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("EST"));
	Calendar utc = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

	Date startdate = Date.valueOf("2006-01-01");
	cal.setTime(startdate);
	utc.setTime(cal.getTime());

	String ins = "INSERT INTO t (datestamp) VALUES ( ?)";

	PreparedStatement stmt = m_conn.prepareStatement(ins);
	try {
		// Use the UTC Calendar to create a new timestamp and the target
		// Calendar to adjust it to the correct timezone and set the
		// timestamp value.
		Timestamp tstamp = new Timestamp(utc.getTimeInMillis());
		stmt.setTimestamp(2, tstamp, cal);

		stmt.execute();

		m_conn.commit(); // commit all the rows.

	} finally {
		stmt.close();
	}
}

 
> By the way, a word of warning: if your program is multi-threaded, don't 
> try to share a Calendar instance globally like this (for "cal", or for 
> "utc", for that matter). Calendar is not thread-safe. Always allocate a 
> local Calendar instance on stack and use/modify it on the spot.
> 
Thanks for the warning; it's not multithreaded at the moment.

dan.





-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]