Re: Dates on Linux vs. Windows - Resolved
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Jerry,
On 1/9/20 1:15 PM, Christopher Schultz wrote:
> You should write yourself some small tests in Java to try
> everything [you've] 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.
I wrote some code for this. I'm attaching it below.
What you'll find is that, no matter what:
1. NOW() always returns a timestamp which is in the client's session
time zone (not the JVM's time zone)
2. Timestamp values are not re-zoned at any stage
3. The zone offset of the java.util.Date object returned by
ResultSet.getDate|Timestamp always matches the JVM's time zone
#3 is where the confusion is coming from, I think.
If you SELECT a date that should be in America/Chicago and your JVM TZ
is America/Chicago, then all is well. But if you change the client
session's time zone and SELECT a date that you expect to be in the new
time zone, then it depends whether this was SELECT NOW() or SELECT
field FROM table. The former will be totally wrong (wait, NOW()
returns *tomorrow*?). The latter will give you whatever date was
stored (without re-zoning it) and your java.util.Date object will have
the America/Chicago zone offset.
Hope that helps,
- -chris
CUT
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;
public class JDBCDateTest {
private static SimpleDateFormat df = new
SimpleDateFormat("-MM-dd HH:mm:ss zzz");
/**
* Sorry for the bad CLI argument interface; this is an example
* and not really worth fleshing this all out. Unfortunately,
* it means havig to put your password on the command-line :(
*/
public static void main(String[] args)
throws Exception
{
String url = "jdbc:mysql://localhost/test";
String driver = "com.mysql.jdbc.Driver";
String username = System.getProperty("user.name");
String password = null;
Connection conn = null;
Statement s = null;
ResultSet rs = null;
int argindex = 0;
while(argindex < args.length) {
String arg = args[argindex++];
if("--url".equals(arg) || "-U".equals(arg))
url = args[argindex++];
else if("--driver".equals(arg) || "-D".equals(arg))
driver = args[argindex++];
else if("--username".equals(arg) || "-u".equals(arg))
username = args[argindex++];
else if("--password".equals(arg) || "-p".equals(arg))
password= args[argindex++];
else {
System.err.println("Unknown argument: " + arg);
System.exit(1);
}
}
try {
// Register JDBC driver
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
// First, dump some time zone information
TimeZone tz = TimeZone.getDefault();
System.out.println("JVM default time zone: " + tz.getID());
s = conn.createStatement();
rs = s.executeQuery("SELECT @@global.time_zone AS
server_zone, @@session.time_zone AS session_zone");
if(!rs.next())
throw new IllegalStateException("Could not fetch
server time zones");
System.out.println("Server time zone: " +
rs.getString("server_zone"));
System.out.println("Session time zone: " +
rs.getString("server_zone"));
rs.close(); rs = null;
System.out.println("= SELECT NOW() with default time
zone ");
rs = s.executeQuery("SELECT NOW() AS now");
if(!rs.next())
throw new IllegalStateException("SELECT NOW returned
no rows");
dump(rs.getDate("now"));
dump(rs.getTimestamp("now"));
rs.close(); rs = null;
System.out.println("= SELECT NOW() with +10:00 time
zone ");
s.executeUpdate("SET time_zone = '+10:00'");
rs = s.executeQuery("SELECT NOW() AS now");
if(!rs.next())
throw new IllegalStateException("SELECT returned no
rows");
dump(rs.getDate("now"));
dump(rs.getTimestamp("now"));
rs.close(); rs = null;
System.out.println("= SELECT explicit date with
+10:00 time zone ");
rs = s.executeQuery("SELECT TIMESTAMP('2020-01-09
14:14:00') AS date");
if(!rs.next())
throw new IllegalStateException("SELECT returned no
rows");
dump(rs.getDate("date"));
dump(rs.getTimestamp("date"));
rs.close(); rs
Re: Dates on Linux vs. Windows - Resolved
-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
>>> 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 -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
Re: Dates on Linux vs. Windows - Resolved
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
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.
Re: Dates on Linux vs. Windows - Resolved
-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
> 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: users-unsubscr...@tomcat.apache.org
>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>
>>
>
-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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Dates on Linux vs. Windows - Resolved
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Jerry, On 1/8/20 12:05 AM, Jerry Malcolm 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. Glad you are all set, though I'm not sure I agree with your solution... and possibly your conclusions as to why/how it's "fixed". > And the winner is the JVM timezone. !! > But as I described earlier, changing the OS timezone made the JVM > also go to Central as well. It's slightly more complicated than this. The system has a time zone. Each user has their own time zone which defaults to the system time zone. The user launching the JVM chooses the time zone for the JVM. So the default progression looks like this: 1. Explicitly set at JVM launch using -Duser.timezone 2. Explicitly set by user using TZ environment variable 3. Inherit from system time zone > But the JVM apparently assumed the database was in GMT so it > subtracted 6 more hours off the already-central time from the db. The JVM doesn't do this. The JDBC driver is responsible. And I still think that Connector/J is doing the right thing. When you connect via Connector/J, the driver grabs the time zone from the server and figures how (and if) to convert time values between the two. > 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??? :-) If you can login to a shell on the RDS instance, you can change the system time zone. You can also change the db time zone in the db config if you want it different from the system (which you do not want IMHO). - -chris -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4WW1EACgkQHPApP6U8 pFgvcRAAiPPdACvG+kUv0paR9XGIZ4NsIl8y6ujxx+vvCqzjyzJ7QmsWifDLDeWq DALbvpK1JR6tlXcaQPGRV18yI8SkfDUBi/2gCpy7pjgYqXCO/h772QQ+qWqpiC8o S402a5GctvdXqD2Kd2xX2wK3oiHGqlQrzCvabt/Fgt4AQOIQJjX46evYu46X1mjZ BZ7utdZBeApK+iKDv0N/1yFS1NAEIqms06hnsYHcEqYAHkxTr1yyQkdFAUmVI3nV uPRagXEN/+IAOsOqULK69axK6ioesMZErGOWRbMFi/3WOkHaaI0eaor2OUQhoSK/ X7L8hy24mMWrXOV+LBmtw5tRj0K7eAeW30+dZFcYcWR4Hb50whvDlfnyqIT1/SSk Z3HtrqYT4QIsorU5/uifN2yDjVfuIqu8YDS5/BW8BWsa0hGaDbqR2JdszeR0t4Nf judacTtPVZThbqfoWsmvRNu0+chQITaZUpEtlSllNzlxmBcwYhsdPO8F7AWzh5i/ gxlbI6KMTO9BCm/0x4QprrROBNr9Zjnas6vXGVuijQDL3wcjQuVdfJKJlQ9OT3pw abi0Z/f5y66mCpN1qenvY328c9XbkQk+0NK1EkdyNLVCmKT1qFvYPU9XRjNmpXRB OvDnakziRvyDU/BMLLX7bGhr/YO3WGu1YvObRyFIFLy8gWjnHVs= =N+gY -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Dates on Linux vs. Windows - Resolved
On 08.01.20 06:05, Jerry Malcolm wrote: > 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??? :-) That's why my personal general recommendation is to run all server code and OS, and especially all stored data, in UTC only. Optionally, for selected output (e.g. website display) have the application explicitly convert to a timezone, dependent on the current user (see Christopher's excellent answer). It's work, but it's what needs to be done. There is no "it just works" when handling time without being explicit - especially in the web / server world, where people access from different timezones. Desktop applications can be more relaxed (but shouldn't). Granted, it's easier for me with UTC being one or two hours off during the year, but the advantage is: UTC is "forward only" - there's no daylight saving back and forth, no need to interpret anything, just the plain timestamp. On the technical side (logs) that's what I want, even if it requires a bit of mental math. I'm not always remembering to set all of this right away, but it's the first thing I retroactively do once I run into those problems. Olaf - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Dates on Linux vs. Windows - Resolved
>From my past experience with dates and timestamps, it helps to pass the time zone as a jvm parameter when starting tomcat -Duser.timezone=Europe/London On Wed, 8 Jan 2020 at 05:05, Jerry Malcolm 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: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >
Re: Dates on Linux vs. Windows - Resolved
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.. 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. 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. On Wed, 8 Jan 2020 at 06:05, Jerry Malcolm 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: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > > -- Johan Compagner Servoy
Re: Dates on Linux vs. Windows - Resolved
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: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
