-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Jerry,
On 1/8/20 6:24 PM, Jerry Malcolm wrote:
>
> On 1/8/2020 4:47 PM, Christopher Schultz wrote:
>> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
>>
>> Johan,
>>
>> On 1/8/20 3:28 AM, Johan Compagner wrote:
>>> So you moved once the database to a different timezone (that
>>> had say that 6 hour difference) then the behavior is
>>> correct...
>>>
>>> Its very weird but that is default behavior of the normal
>>> datetime columns that are created if you move stuff around the
>>> database somehow remembers at what timezone the datetime was
>>> inserted and will convert the millis accordingly..
>> The database doesn't store timezone information. It just has a
>> DATETIME which is zoneless. If you INSERT .. VALUES ('2020-01-08
>> 17:45:00') then that's what gets written. It doesn't do any
>> translation. That's why it's important for the client to
>> understand the context of all datetime values and adjust
>> accordingly.
>>
>>> Its the same as if you have different clients connecting to the
>>> same database over different timezones they will al see the
>>> same date as a string (so the formatted date) instead of
>>> really having the same millis after 1970 utc.
>> Correct.
>>
>>> I always find this very very weird. But i guess this is the
>>> difference between database types "timestamp with timezone" and
>>> "timestamp"
>>>
>>> So moving the database or moving the client (app server) with
>>> existing data is very tricky.
>> If the client always adjusts both ways, there shouldn't be any
>> problems. Ignorant clients will always cause confusion.
>>
>> - -chris
>>
>>> On Wed, 8 Jan 2020 at 06:05, Jerry Malcolm
>>> <techst...@malcolms.com> wrote:
>>>
>>>> First of all, a big thank you to everyone who responded to
>>>> this one. I doubt I'd have figured it out for days without
>>>> your guidance and help.
>>>>
>>>> And the winner is.... the JVM timezone. But the problem was
>>>> NOT that the JVM wasn't set to US Central time. The problem
>>>> was that it WAS set to US Central, apparently inherited from
>>>> the Linux OS TZ. There was no parameter on the tomcat java
>>>> command that set the timezone. So I added one and set it to
>>>> America/Chicago. No change. But since it appeared we were
>>>> already double-dipping and converting from GMT to central
>>>> twice (i.e. subtracting an additional 6 hours), I figured
>>>> ok.... tell the JVM to stay in GMT and not do any
>>>> conversions. So now, the database returns Central time
>>>> dates and times, but JVM no longer thinks it needs to convert
>>>> again to 'more central'.
>>>>
>>>> This is about as convoluted and ugly as it gets. And I don't
>>>> make any claims of thinking I can give a rational explanation
>>>> for why it works this way. But it's on to fight a battle on
>>>> another hill now.
>>>>
>>>> Just to summarize for anybody who comes along with a similar
>>>> problem.... I original set the timezone of mySQL RDS
>>>> instance to Central time when I created it months back
>>>> (unchangable after it's set). I set my Linux timezone to
>>>> Central as well in order to make my log files have entries
>>>> with the correct timestamps. But as I described earlier,
>>>> changing the OS timezone made the JVM also go to Central as
>>>> well. But the JVM apparently assumed the database was in GMT
>>>> so it subtracted 6 more hours off the already-central time
>>>> from the db. I guess the real error was not initially
>>>> leaving the MySQL RDS in GMT. But since that's not
>>>> changeable without recreating a whole new RDS instance, the
>>>> next option is what I did with the jvm. Makes total sense,
>>>> right??? :-)
>>>>
>>>> Thanks again.
>>>>
>>>> Jerry
>>>>
> Chris, I really want to get this right. I understand that enough
> wrongs in even numbers may result in a 'right'. But I'd really
> like to understand this. So bear with me on this. It makes sense
> that the database doesn't store timezone info in data fields
> unless the tz is part of the data itself. But then what is the
> significance of the RDS timezone and/or setting mySQL timezone
> values if the database is zoneless.
In cases where the timezone matters (e.g. TIMESTAMP fields in MySQL),
the JDBC driver will convert the date correctly.
I would encourage you to inspect the time zone related fields in the
java.util.Date and/or java.sql.Date and/or java.sql.Timestamp values
that you get back from e.g. ResultSet.getDate and
ResultSet.getTimestamp. You should find that the field-values (e.g.
month/day/year/hour/minute/second) match those of the database *when
they are adjusted according to the time zone info in the object*.
If you print a java.util.Date object (that is, using Date.toString(),
you should get something like this:
Thu Jan 09 12:10:05 EST 2020
If you just print yyyy-MM-dd HH:mm:ss you won't see that TZ info at
the end. Worse, because both the java.util.Date *and*
java.text.SimpleDateFormat objects can have separate time zone
information, you can be surprised when you format the date:
Date date = ...
System.out.println(date);
System.out.println(new SimpleDateFormat("yyyy-MM-dd
HH:mm:ss").format(date));
You'd expect:
Thu Jan 09 12:10:05 EST 2020
2020-01-09 12:10:05
But it's also possible to get something different if your date object
has a different zone offset. It's not possible to *set* the timezone
offset directly, but you can tweak it with SimpleDateFormat when
parsing it. Forget about that for a minute and concentrate on
something a little less difficult to code-up in a test-case: changing
the time zone for the formatter.
Like this:
Date date = ...
System.out.println(date);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
df.setTimeZone(TimeZone.getTimeZone("America/Chicago"));
System.out.println(df.format(date));
The output is:
Thu Jan 09 12:13:53 EST 2020
2020-01-09 11:13:53
The first line shows EST because that's my computer's system time, and
so "new Date()" sets that offset. It's possible the date was read
elsewhere, parsed in another user's time zone, etc. But if you just
print the field values you can become confused. So to reduce
confusion, always print the time zone:
Date date = ...
System.out.println(date);
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss zzz");
df.setTimeZone(TimeZone.getTimeZone("America/Chicago"));
System.out.println(df.format(date));
Thu Jan 09 12:15:11 EST 2020
2020-01-09 11:15:11 CST
So now you aren't confused that you have lost an hour.
By default, java.util.Date and java.text.SimpleDateFormat will inherit
the JVM's time zone. As mentioned previously, that can change
depending on the environment when the JVM was started. It is best to
*never rely* on those values being something stable, predictable, etc.
You should always know what the current user's preference is, or at
least always force all display to use a known time zone.
So, for example, if your application usually lives in America/Chicago,
make sure that you do one o the following:
1. Explicitly set the JVM's time zone to America/Chicago
2. Explicitly set the time zone for all SimpleDateFormat objects to
America/Chicago
3. Always show the time zone in every timestamp you print
The great news about java.util.Date is that it dumps its time zone
when it's printed using toString(). The bad news is that (a) you
should pretty much always use SimpleDateFormat and (b)
SimpleDateFormat makes it easy to (c) forget that it's got its own
time zone and (d) programmers often forget that other time zones exist.
> But whether or not tz info is present, there is an assumed
> timezone for all date/datetime/etc fields written to the database.
Yes and no. At some point, your code (or the JDBC driver) will convert
your java.sql.Date (or java.util.Date) object into a DATETIME field,
which you can kind of thing of as text: yyyy-MM-dd HH:mm:ss. The
database just (simplifying here a little) stores the text and gives it
back to you. If you have a CLI that is willing to interpret DATETIME
values for you in your own time zone (value of the TZ variable when
e.g. you launch mysql CLI), then it needs to know what the "storage
time zone" is, and really there is none. I don't believe MySQL does
that kind of thing, but I haven't performed any deliberate tests.
When you "SELECT date FROM table", and then grab the "date" field
using ResultSet.getDate("date"), you'll get a java.sql.Date object
which was been poisoned with a time zone offset -- even if it's zero
for e.g. UTC.
Let's say that it IS zero for UTC, or, because java.sql.Date shouldn't
have any timestamp portion even though it extends java.util.Date which
absolutely DOES have a timestamp (and time zone!), it gets set to zero
because that makes sense for a zone-less-timestamp-less object. What
happens when you print it using SimpleDateFormat yyyy-MM-dd HH:mm:ss?
One that was created with a JVM time zone of America/Chicago? Well,
you'll see that the timestamp on output will differ from the
java.sql.Date's timestamp value by a few hours because the
SimpleDateFormat will adjust the output by the difference in time
zones between the two (java.sql.Date and SimpleDateFormat). If you
explicitly set the time zone for the SimpleDateFormat to UTC, then the
hour/minute/second will match what you expect. Or if you print the
time zone ("zzz") along with the rest of the field values you'll at
least know what time zone the SimpleDateFormat was using at the time.
If you print the time zone, and it's correct, but the
hour/minute/second looks off, then there is a conversion elsewhere
which is tripping you up.
> In my code, for years, when I write a date, datetime, etc field,
> it's always been the date/datetime of central timezone. Until now
> I haven't had a need to have clients in other timezones. So it has
> never been a problem.
This is the point where most programmers say "hmm... I've never had to
think about this before" and go down this rat-hole. :)
> So I guess I could say my database is 'central time' since years
> worth of date and datetime fields were written as the date/datetime
> values of central tz. There is no reasonable way to alter that
> massive amount of data now.
You might not have to. Let's try a little experiment:
mysql> CREATE TABLE time_tests ( f DATETIME NOT NULL );
mysql> INSERT INTO time_tests VALUES (NOW());
mysql> SELECT * FROM time_tests;
mysql> SET time_zone = '-06:00';
mysql> INSERT INTO time_tests VALUES (NOW());
Then:
mysql> SELECT @@session.time_zone, @@global.time_zone;
mysql> SELECT * FROM time_tests;
Here's what I get:
+---------------------+--------------------+
| @@session.time_zone | @@global.time_zone |
+---------------------+--------------------+
| SYSTEM | SYSTEM | (SYSTEM == EST for me)
+---------------------+--------------------+
+---------------------+
| f |
+---------------------+
| 2020-01-09 12:31:16 |
| 2020-01-09 11:43:30 |
+---------------------+
So the date I inserted first is not re-interpreted into the client's
new time zone. Only the value of NOW() is changed.
So if you "change" the time zone of your server, all of those existing
timestamps will not have their values changed. Let's try it and see
what happens.
Stop mysql, add this to my.cnf:
[mysqld]
default_time_zone='+10:00'
Restart mysql.
mysql> SELECT @@session.time_zone, @@global.time_zone;
mysql> SELECT NOW();
mysql> SELECT * FROM time_tests;
+---------------------+--------------------+
| @@session.time_zone | @@global.time_zone |
+---------------------+--------------------+
| +10:00 | +10:00 |
+---------------------+--------------------+
+---------------------+
| now() |
+---------------------+
| 2020-01-10 03:54:46 |
+---------------------+
+---------------------+
| f |
+---------------------+
| 2020-01-09 12:31:16 |
| 2020-01-09 11:43:30 |
+---------------------+
So, the stored dates are stable. They aren't stored as string values,
but you can think of them that way.
(Remember to change your db back to whatever time zone you want.)
> But just to educate me, it appears that to be really
> timezone-enabled, I should convert any date/datetime I write to the
> database into GMT, and then declare the database to be a GMT
> database.
It doesn't matter which zone you choose. UTC is better than GMT (they
are not the same thing) if you want to pick the "right" one.
> Once that is done, I can now tell the jvm to use central time, and
> it will re-convert the stored GMT back to central (or whatever tz
> the server is declared to be). Am I on the right track?
No. The stored dates will be read into your JVM using their stored
values. The driver will assume that they are in whatever time zone the
JVM is using. That is, the text will be read from the database and
interpreted as being "here" ... whatever that is. If you want to
convert that to another time zone (like America/Chicago), then you'll
have to make sure you use SimpleDateFormat to show dates to your users
as appropriate.
You should write yourself some small tests in Java to try everything
you're read here. Just grab a date value from the database and inspect
the object you get back. Mess with the time zones of various
components, see what changes, and how to need to handle the those
situations so the user always sees what they expect to see.
If you perform the same SELECT statements in Java, you'll find that
the values returned are the same, though the time zone offsets for the
java.sql.Date objects may fluctuate and/or be a little surprising
depending on various factors.
What you CAN NOT do is allow a user to enter a date/time value that
you don't adjust to your own internal time zone. Otherwise, when I (in
EST) enter a timestamp and you read it (in CST), they'll always be an
hour off and one (or both!) of us will be confused. Also, if you use
NOW() in your queries, the time zone of your mysql client (the session
time_zone) had better agree with whatever "internal time zone" you
intend to use for all storage, because the server will give you a
(zoneless!) value for NOW() every time.
> And the final question/assumption... it appears that the jvm on
> Windows does not inherit the OS timezone as it does in Linux. My
> code on Windows does not convert db values to central time even
> though windows is set to central.
I'm not sure there is a canonical environment variable for time zone
on Windows like there is in the UNIX world. Perhaps that's why it
doesn't do that. But if you change your Windows user's time zone (like
in the control panel or whatever), I suspect the JVM's time zone will
change accordingly.
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4Xbd0ACgkQHPApP6U8
pFjjoRAAmRpEzX0pHLqWGCnrSc54YmHXHZzhAex0GZVOxCU7cCxlAyCn1QKiCLT7
a1foF+ilZNWCxCgG5JOISn5woRYsF6WN0KO2nmNqZgfDHU61aNcmGl4jT90/95Tc
RqscRZDq109Y3tVPPVCfTKizjBU1wZiyNyV5Bw/+FY59XDYViBC5yPnefN3TcaqO
qzoQlf2OOmXqG/WPAvq4e5yLL7LuXEHDPlY5KJUvXE2Y/vcdiKvLPLPORwRQx6u0
bHE3zRQr+jX+h6912UtKdZg9qdrQ2bDmJ8+KazDGdxep3Fpf4g69yC+4xgys3uUQ
v68ULSdDU3GZyCSzPNAl2HxSFA/bJiaQmvkc3uXoNssk0KQ1i5AXrBGGBYqcX0kH
je1kT3oR2oyFKCSaYszvR2Xx8LtRcPLU/xPnW0aheg44q0I9JKpO+3ydB53k2d0X
Nj+yMYj3oSmt6GtudkwpuiOk2Obed3S9qvteS2Uipt/keRX50kVHy/YmjtDwu5HD
LiGA1Zg3dhzlaikCHXUPC8jL5JFuJEVkzeSUymo4K7716mtDoqPO5hAwfWZ3fBZl
WY5jGLuL7buHKG3lDBdmComFbKU4KO982sPcalFT0l6PM4nduVUYSdcrxEi/rV3A
/VADSgAXc8WOkgJQ/vwoWR5Fl0LNaCdlQJlr6xa082nfW1ujSXQ=
=SoFz
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
