-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Shankar Unni wrote:
> I asked this on the JDC site, but didn't get much of a response there.
> Basically, this is about the precise semantics of the "Calendar"
> argument to PreparedStatement.setTimestamp().
>
> Here's what I'd like to do: since most databases don't understand
> timezones, I'd like to convert timestamps to UTC equivalents. E.g. I'm
> in the Pacific timezone, so if I have a timestamp corresponding to Jan
> 20, 12 Noon PST, I'd like the database column to be recorded as "Jan 20,
> 8PM" (because that's what the timestamp would read if it were formatted
> using a UTC calendar).
>
> I'm using
>
> Calendar utc_cal = Calendar.getInstance("UTC")
>
> and am calling
>
> PreparedStatement ps = ...;
> ps.setTimestamp(n, ts, utc_cal);
>
> What actually happens is that:
>
> * With Oracle, the time gets written into the database as 2003-01-20
> 20:00:00 (what I'd expect).
>
> * MySQL (connector/J 3.0.4) on the other hand, interprets this as "I'm
> saying that I'm in UTC, and so convert it to what the server expects",
> so it actually does the opposite transformation: 2003-01-20 04:00:00 is
> what gets written into the DB.
>
> Who's correct?
MySQL does not have TZ support for datetime data. Connector/J (if you
have set 'useTimezone=true' will convert from either the client timezone
(if no Calendar is specified) or the given calendar to the server's
timezone when using PreparedStatements. You must then use an equivalent
calendar instance when using ResultSets.getTime/Timestamp() to get the
value that is stored to be correct for a particular timezone. I feel
this is closer to what the spec is saying (although I agree it is
dangerously ambigous, they don't even provide the results of in their
example, which would make it obvious how they meant it to be interpreted).
From the spec:
"The JDBC API follows the Java platform’s approach of representing dates
and times as
a millisecond value relative to January 1, 1970 00:00:00 GMT. Since most
databases
don’t support the notion of a time zone, the JDBC 2.1 core API adds new
methods to
allow a JDBC driver to get/set Date, Time, and Timestamp values for a
particular time
zone using a Calendar."
I have interpreted this to mean that the calendar instance represents
the TIMEZONE component for the given datetime value, i.e. "Treat the
milliseconds value in this Timestamp as in the UTC timezone" in your
case, not that the value should be converted from the client timzeone to
the timezone given in the Calendar instance before being sent to the
database (where it might be converted once again).
BTW, how did you create a timezone in PST? All datetime values are in
UTC in Java, by default. If you use the SimpleDateFormat without
specifying a timezone (like by calling .toString() on it), it will be
_converted_ to the default timezone.
For the record, Oracle converts the timestamp value to the database
timezone internally when stored, and to the session timezone when retrieved.
-Mark
- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <mark@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+LhYUtvXNTca6JD8RAsk8AJsHs6fv3agJte/7gsMlU1dnBPUCDQCgtkUm
JYZihlnfEDQyntO3BwztN1A=
=TlO1
-----END PGP SIGNATURE-----
