-----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 <[email protected]>
> 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
>>
>>
>> ---------------------------------------------------------------------
>>
>>
To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4WW/wACgkQHPApP6U8
pFgyOw//e1s40WZp2BXR93OmY/B+1ujrJGqPBzTZ6xYwRREqYfc14D1XhdDV9QuY
NH9p99rZLC2fpjxcWsoLDl82sDr/31aM5mkR4XRvOu93EAWO8jwMdmaFwxI05Kmn
S4ALFYjlFvgVS6usjKHjxeUcOihNJncEimdexRLfzzxCcj3qWaetr6j11azIqWn6
zWxGqWIq5dyfr43zwA/lTaoEOzVAOzzhGGzTrK2kQLNz7KKVnSID39BFxKo2AeYr
A22r/RfPsxuChnO22U32tWD2ulwO7kzOm0hfgzND9efJkRrN50gO746HdL5qrqyv
rfUrPpWsJurWj393WQzMwN04WsLOIAemQ8WF+djJmnrYX0k9khwiMRPxKG0QPBz3
8ukX2Y5jSDQTlcmw21FSWtnWZ6Q8CtGNXwxQPcHLC3yp1UIwMzndPqCq9Qs0JQ+U
RHNng/Vq2COzIAMWMdZWPVFPb+gffQGoBEk7dDXfNRuiyG5JE7bzmb5+0EYOpJA3
piexrJoR1J91rrLhQ62wOGpO26c9GlO3v+VP4/WcOk1amjdN4gzaXTRx2ncq6GsD
Te7ZfucErcmwO4YROPG8ZHp8yoGInhZ98mj2yyteo618U0a/v9F/A4GhiWjjyD76
5ImOvoIGLXTSfDyZUI4UhPWVhQVqvAdm6zmG4w63cJAAaonMpzc=
=U0UR
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
