Timezone jumping
I'm having trouble getting my java application to do something, and I wondered if I might not get some help. Thank you for reading.
I've got a java application that reads (among other things) dates and times from a mysql database. When I pull the times from the database and then print them, they are exactly the same as what was put into the database. When I debug it, I see that the java.util.Date objects that are returned from the database have a timezone of America/Los_Angeles, which is right, as I'm in PST. I would, however, like to store all of my dates and times in the database as GMT, and then interpret them on a per-user basis to the local timezone.
So I changed the times in the database over to GMT, but now I'm realizing that there is no way for me to instantiate a Date in any timezone other than PST without changing the environmental variables. I worked a trick out with a Calendar class where I could change the timezone, but it's extremely cumbersome, and I'd like it if I could change the Date over directly instead. When I use the DateFormat to change the timezone (to GMT) it comes out very odd: 16 hours ahead. It believes that the values it receives from the db is PST, so that's an 8 hour time difference, and then it believes it has to add on 8 more hours to get back to GMT, when really the value starts as GMT, and should simply subtract 8 to get to PST. By the way, the value in the database is 8 hours ahead of our time here, which accounts for the first half of the discrepancy.
So here's my question: if the date is being stored in GMT inside of the Date class, can I get at that directly without the methods changing it to "local time" on the way out?
(I just realized this might be a bit confusing. The reason that I'm changing timezones and don't want the environmental timezone is because this is a JSP application, and I want the users to be able to set their own timezones. And as a small aside from that, it would be splendid if I could get at a user's local timezone from the locale, or anything in the request object, but that isn't possible, right?)
Thank you kindly,
Stephen
[2175 byte] By [
Mizutsukia] at [2007-11-27 4:00:14]
> I'm having trouble getting my java application to do
> something, and I wondered if I might not get some
> help. Thank you for reading.
> I've got a java application that reads (among other
> things) dates and times from a mysql database. When
> I pull the times from the database and then print
> them, they are exactly the same as what was put into
> the database. When I debug it, I see that the
> java.util.Date objects that are returned from the
> database have a timezone of America/Los_Angeles,
> which is right, as I'm in PST. I would, however,
> like to store all of my dates and times in the
> database as GMT, and then interpret them on a
> per-user basis to the local timezone.
If your columns are DATE or DATETIME type, then TZ is not an issue for storing and retreiving, only for displaying and for parsing user-entered dates/times, and java.util.Date (and sql.Date, ant Timestamp) does not have a TZ. It's simply millis since a fixed point in time. What that point is does not depend on what TZ you're currently in.
Use SimpleDateFormat and set the SDF's TZ before calling sdf.format().
> So I changed the times in the database over to GMT,
No, you didn't, or if it was even possible to do so, you're storing them wrong.
If I'm in PST and you're in EST, and at the same instant in time--1:00 my time, 4:00 your time--we create a java.util.Date, that Date's backing long will have the same value. For any reasonable DB, when we store that Date into a DATE or DATETIME column, my 1:00 and your 4:00 will be stored as the same value, or at least will appear to be that way.
TZ only matters for parsing and formatting, not for storing.
Date doesn't have a timezone, not even PST, so your wish to instantiate a Date with a particular timezone isn't going to be granted. Probably you have seen Dates formatted as Strings in a particular timezone, and you assumed that the Date was therefore "in" that timezone. Ain't so. You could read the documentation where it says that.
You can assign a timezone to a SimpleDateFormat object if you want a String representation of a Date in a particular timezone. Don't mess with Calendar objects to change a Date from one timezone to another.
As for the timezone of your client, obviously you can't get a timezone from a Locale. (What's the timezone for Locale.US?) But Javascript can find out the client's offset from GMT and you could upload that to your server. Or you could write some Javascript to adapt your timestamps to the client's GMT offset.
That's odd. You say that java.util.Date doesn't have any timezone in it, but when I insert a breakpoint and look at the contents of the object it has a sun.util.calendar.BaseCalendar and that has a timezone in it. I always just assumed that that timezone must be what I was having trouble with.
As for the DB, I assumed that mysql was timezone agnostic, and simply took times in and spit them out exactly the same. Are you saying that when I put 8AM in, if a person in england had that database that same value would read midnight? If so, is there any way to make it timezone neutral? It's complicating things.
Right, ok, so that's not the problem, then the problem is display. I put 8 AM in the database, when I call for it, I get 8 AM back. When I use a DateFormat set to GMT, I get 4PM back, but shouldn't I get midnight back?
Were I better with JavaScript I might do that. I think I'll just stick with asking the user what he/she wants, instead. Thanks for the suggestion, though.
Thank you.
Message was edited by:
Mizutsuki
> That's odd. You say that java.util.Date doesn't have
> any timezone in it, but when I insert a breakpoint
> and look at the contents of the object it has a
> sun.util.calendar.BaseCalendar and that has a
> timezone in it.
Date != Calendar. A Date is just a long. Like I said, right this second, if you, I, somebody in Chicago, somebody in NY, somebody in London, and somebody in Tokyo all create a Date, the long will be the same for all of us--number of millis since 1/1/1970 00:00:00 GMT. (Note that this does NOT meant that Date "has" a TZ. It does not.) The TZ comes when a String is created from the Date.
> I always just assumed that that
> timezone must be what I was having trouble with.
> As for the DB, I assumed that mysql was timezone
> agnostic, and simply took times in and spit them out
> exactly the same. Are you saying that when I put 8AM
> in, if a person in england had that database that
> same value would read midnight? If so, is there any
> way to make it timezone neutral? It's complicating
> things.
It is TZ-agnostic. You're overcomplicating it.
// In LA, I do
date = new Date(); // Ignore util.Date/sql.Date/Timestamp distinction for now
S.o.p(date); // prints out Thu., 9:53 a.m. PDT
ps = con.prepareStatement("insert into whatever values (?)");
ps.setDate(1, date);
ps.executeUpdate();
// In London, you do
rs = execute("select * from whatever"); // yeah, I'm fudging the jdbc stuff. Just lazy
rs.next();
date = rs.getDate(1); // this Date wraps the same long as my original in LA
S.o.p(date); // prints out Thu., 5:53 p.m. BST or whatever
sdf = new SimpleDateFormat(some format);
sdf.setTimeZone(new york);
S.o.p(sdf.format(date)); // prints Thu., 12:53 p.m. EDT
The DB stores "N millis since the epoch", or something equivalent. My LA Java and your London Java read that value and interpret it as the same point in time. When we print it, it converts to the appropriate TZ.
> As for the DB, I assumed that mysql was timezone
> agnostic, and simply took times in and spit them out
> exactly the same. Are you saying that when I put 8AM
> in, if a person in england had that database that
> same value would read midnight?
I don't know how MySQL stores timestamps. And I don't know what happens when people look at timestamps in MySQL. That really isn't relevant to Java anyway. The JDBC driver will convert whatever is in MySQL to a Date object, which (as the documentation says) is simply the number of milliseconds since a certain instant of time. When you "look at it", something formats it into a String using your default timezone. When somebody in England "looks at it", something formats it into a String using their default timezone. Obviously the two people see different-looking outputs, but those outputs represent the same point in time.
In your example, presumably you are putting in 8 AM in your timezone. That's the same as 4 PM in the person in England's timezone. But the fact that you're using a database is irrelevant to your question (or it should be). If you display the 8 AM time in your timezone, you see 8 AM. If you display it in Europe/London, you see 4 PM. They are the same instant in time.
> The DB stores "N millis since the epoch", or
> something equivalent. My LA Java and your London Java
> read that value and interpret it as the same point in
> time. When we print it, it converts to the
> appropriate TZ.
You mean, even if I log into the DBServer and use the mysql command line tool and do "select * from xyz", you're saying even that will be interpreted for my timezone? If I were able to peel back the database interface, it would be milliseconds even in the database, excluding all java?
again, thanks so much.
> > The DB stores "N millis since the epoch", or
> > something equivalent. My LA Java and your London
> Java
> > read that value and interpret it as the same point
> in
> > time. When we print it, it converts to the
> > appropriate TZ.
>
> You mean, even if I log into the DBServer and use the
> mysql command line tool and do "select * from xyz",
> you're saying even that will be interpreted for my
> timezone? If I were able to peel back the database
> interface, it would be milliseconds even in the
> database, excluding all java?
Like DrClap says, I don't know how exactly the DB stores it, but that's irrelevant. From your blackbox p.o.v.--your Java app that sees the DB through the drivers--it's as if the DB is storing that same millis-since-epoch that the java.sql.Date stores.
Now, if the MySQL client is not completely retarded, then, yes, if I run the myql client in LA and you do in London, when we execute the select, I'll see 08:00 and you'll see 16:00.
Remember, the TZ is *only* present when formatting or parsing, not when storing. It's not an inherent property of the Date/Timestamp. You as a human seeing the time you expect--08:00 or 16:00--is entirely on the client to know what TZ you're in (or wanting to view in) and formatting that same TZ-agnostic value accordingly.
When I insert in one timezone, and then pull out in another it comes out the same. The database has no idea what timezone it is, so the problem is Java assuming that the time I bring in from the database is local. The JDBC driver is assuming the value I'm pulling from the database is local, and adjusting it's milliseconds accordingly. Then when I print as GMT, it's showing me what's actually stored in the Date object, which is NOT what is in the Database, but instead 8 hours removed. I think this is a complication with the JDBC drivers.
Stephen
> When I insert in one timezone, and then pull out in
> another it comes out the same. The database has no
> idea what timezone it is,
Can you give sample code like what I did above that shows what you're doing?
What is the SQL type of the column?
How are you inserting, extracting, and printing the date?
