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.
But whether or not tz info is present, there is an assumed timezone for
all date/datetime/etc fields written to the database. 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. 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. 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.
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?
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
