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
On 1/7/2020 6:53 PM, Jerry Malcolm wrote: >> If your systems always use the same time zone to read and write the data, it isn't a problem. Terrance, thanks for the info. In my case I do only have one timezone (or at least I want to...). Using the string for dates is a good idea. But this is a massive application that's been in production for years with tons of date and timestamp fields spread everywhere across the code and the db. So converting to strings is not a possibility now. It still comes down to the fact that the mysql command line on my linux box gets the date right, but it's converted incorrectly by JDBC and only on the linux box (and works on WIndows) When I first set up the box an installed Tomcat, the default Linux time was gmt. I didn't change the Linux time to central until later. Any chance that tomcat stored the timezone in effect when it was installed and still is using that even though I changed the linux timezone? (Just grasping at straws here). Hi, Jerry- I realize you've found an apparent work-around for the issue but, to be clear, I did not use character columns. The column types were DATETIME. However, when I inserted or updated, I provided string values (e.g. update MyTable set myDateTime = '2020-01-08 06:57:00'). And, when I read the data, I extracted date/time values from the result set using getString (e.g. parseSqlDateTime( resultSet.getString( "myDateTime" ) ). Best regards. -Terence Bandoian - 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
Re: Dates on Linux vs. Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Jerry, On 1/7/20 7:42 PM, Jerry Malcolm wrote: > Summarizing what I know now... when I use the command line on the > linux instance and do a mysql query, I get the correct date (i.e. > the date that I set, the date I wanted, the date that mySQL exports > to SQL file, and the date that appears in Windows tomcat). So this > pretty much rules out the problem being in the mySQL server. I > would think this also rules out the problem being in some timezone > setting in the base Linux system hosting tomcat since I get the > right date in the command line. That only leaves the JVM; JDBC > package, and MySQL connector. The JDBC package and the MySQL connector are the same thing. They will convert correctly from the server's time zone into the JVM's time zone. > MySQL RDS instance is set to US Central timezone. So it's going to > return dates in central time, right? Sort of? See my other reply for ... details. > It appears that jdbc and/or the connector is assuming the db is gmt > and knocking off another 6 hours. Is there some place in the > datasource to tell tomcat the timezone of the database so it knows > not to convert TZ when it doesn't need to? I can't find anything > like that in the datasource documentation. Your JVM is probably in GMT/UTC. The conversion is not the problem. It's just the output that is causing you pain. - -chris -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4VMeoACgkQHPApP6U8 pFjzWRAAlSOt1Yu3jGYUzM0zxfuaSNJ88Tu0QS2Ibt3AtuJEqwvU6LWmjS+B1DpR THxpP/RYEUbEoe9BMxm6aN4qT0DpmP5sWqbceqcOmLH1ACAGalvtX/mLh9auSXk/ +paOkUB6aZ6CNTXwth8O0c89KuKIdydw0jAcJesU32leuKzOsgPlY5Ey5QG/G6Gv YvqY61730vQ573fTzAFX84w10v+Q7+vTGxzS5RgtQcjCK6vH6jXPinzmkTOKSa3E 7gC2yHGJ0yQvRUVNYWqUS8yiBX5GmrO7bXPGxIT5jbfzdlCOUgciElLaoO1WmxTX 1tU6wX5zh3Mm3ACNN6+JRPJXUkJNO5bcfkvVRUkNoyyFyEFMHqg9edDQLMDA3IlU +KTzFLJqEsTvxiZ3t5T5b/POo8uNxleMO23AEzZZYVCTtl9pfPi9d/EtLsbwUhO2 USpLiKhEmiBe0EXFdnXJEBIkaMh7TllVZqeueTgfyMdWq42XIxb6xtfbZhRBjhYT i8tBwOXlTdI6NltW7RA9ucPcCh6esNYRwPcozXlwVBr8yJPNAWtL3y0OCO5/kGTw 73BPCiNcFmm5bR9Qh+2CRrjIzYaEmoLDEccsScfSrLIvne4mrKgyi7FkFbH7tlKk 0IPxvJrwNBjtHLPppZdnqDYaXAQX+j+mAcSbJVU9dFhjv3tBWzM= =3pbq -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
On 1/7/2020 1:13 PM, Jerry Malcolm wrote: > On 1/7/2020 3:09 PM, Michael Osipov wrote: >> Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: >>> This may be more of a Java question than Tomcat. But I'm not sure. >>> I have the same code, talking to the same MySql Linux (AWS) >>> database. I read a date column value in a Tomcat app. After calling >>> resultSet.getDate(...) I printed the date instance and the getTime() >>> value: >>> >>> On windows: 2019-02-01 154900080 >>> >>> On linux: 2019-01-31 154897920 >>> >>> Again this is the SAME line of code in java reading the SAME field in >>> the SAME database. Only thing different is Linux/Windows OS. The >>> date is supposed to be 2/1/2019 and shows that in phpMyAdmin. >>> >>> I've been running on Linux for a few months. But I don't have an >>> extensive background in the specifics of Linux. I'm sure there must >>> be something that is configured differently. I'm at a loss. But this >>> is not a trivial problem. I do monthly billing. My dates need to be >>> accurate. >> >> Have you verified that you aren't tricked by any timezone issues? > Probably so. But how would I know? I was under the impression that > java.sql.Date was timezone independent. Shouldn't it simply convert a > month/day/year value to the number of milliseconds since the epoch? How > would timezone issues affect that? And if I am 'tricked' how do I > 'untrick'. What do I set/change? According to the AWS documentation, there are two places that you have to set manually in order to get the timezone changed universally. 1. /etc/sysconfig/clock This you've already changed correctly. 2. /etc/localtime According to the documentation, you'll need to link /etc/localtime to the appropriate /usr/share/zoneinfo/America timezone file - most likely Chicago. sudo ln -sf /usr/share/zoneinfo/America/Chicago /etc/localtime Also, do you have chrony installed and running on your Linux instance? This is an NTP replacement that the AWS documentation recommends, and will sync your time with AWS time servers. Once you do all of this, you'll have to reboot. Here's a link to the documentation: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/set-time.html For what it's worth, the following quick and dirty code (note, no packages, ma) prints out the correct timezone (Pacific Standard Time) before I made the link and rebooted the machine. Here's the code (no package, bad programming practice). tz.java: import java.util.TimeZone; public class tz { public static void main(String[] args) { System.out.println(TimeZone.getDefault().getDisplayName()); } } $ javac tz.java $ java -cp . tz Pacific Standard Time $ As an aside, on my CentOS 6 system, there are notes in the /etc/sysconfig/clock file: # The time zone of the system is defined by the contents of /etc/localtime. # This file is only for evaluation by system-config-date, do not rely on its # contents elsewhere. So I suspect that part of your system thinks it's UTC and part CST/CDT? . . . just my two cents. /mde/ signature.asc Description: OpenPGP digital signature
Re: Dates on Linux vs. Windows
>> If your systems always use the same time zone to read and write the data, it isn't a problem. Terrance, thanks for the info. In my case I do only have one timezone (or at least I want to...). Using the string for dates is a good idea. But this is a massive application that's been in production for years with tons of date and timestamp fields spread everywhere across the code and the db. So converting to strings is not a possibility now. It still comes down to the fact that the mysql command line on my linux box gets the date right, but it's converted incorrectly by JDBC and only on the linux box (and works on WIndows) When I first set up the box an installed Tomcat, the default Linux time was gmt. I didn't change the Linux time to central until later. Any chance that tomcat stored the timezone in effect when it was installed and still is using that even though I changed the linux timezone? (Just grasping at straws here). - 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
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Zahid, On 1/7/20 4:19 PM, Zahid Rahman wrote: > If you wish to find out if the database connection API is buggy. > > Is the result when you use select query from each of the operating > system same. > > Select column_name from table; > > > If select on both return values are same then likely the database > API is buggy. You have choice of two database connection APIs. > > One API is tomcat specific. The other is vendor neutral. The API that matters is the JDBC API, and the driver you are using. Most people use Connector/J for both MySQL and MariaDB. It is a correct implementation and is reliable. - -chris > On Tue, 7 Jan 2020, 21:09 Michael Osipov, > wrote: > >> Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: >>> This may be more of a Java question than Tomcat. But I'm not >>> sure. I have the same code, talking to the same MySql Linux >>> (AWS) database. I read a date column value in a Tomcat app. >>> After calling resultSet.getDate(...) I printed the date >>> instance and the getTime() >> value: >>> >>> On windows: 2019-02-01 154900080 >>> >>> On linux: 2019-01-31 154897920 >>> >>> Again this is the SAME line of code in java reading the SAME >>> field in the SAME database. Only thing different is >>> Linux/Windows OS. The date is supposed to be 2/1/2019 and >>> shows that in phpMyAdmin. >>> >>> I've been running on Linux for a few months. But I don't have >>> an extensive background in the specifics of Linux. I'm sure >>> there must be something that is configured differently. I'm at >>> a loss. But this is not a trivial problem. I do monthly >>> billing. My dates need to be >> accurate. >> >> Have you verified that you aren't tricked by any timezone >> issues? >> >> >> - >> >> 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/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4VJwgACgkQHPApP6U8 pFj0whAAh5BtS70kaQq/G/CdHIttfjCnkkrSsEqudyKhMuzuSL+TX8OFYYpHEL8g sVsK7KWzoOCWKJa6v0St52vubU0yUh3xvgNv7VpkFkoEVL8+DuXeJrv+rKIcqI4m HxcMwZJIQQhLbTHX0pYDMWN88WaG5tdJIKjQBNXy17JSr4WMTThT4Oiei34QCD7O 9G+Cji2C6gf83AJTlMdihNEh71M/Xh2BUuz10m6CN+M2DN7UikUhU+u6St6AQp/f JTApL7cYS9d9weapGZiTHGwg6nyxj4morHRfT2BCMJq+tyK2u8X+Tim2cPzIyTdj YdtJOJQ3RtZRufS0DYlTVk5+1kHWI7l8KVBo2yo4QuRwoxesigODPNLAVsZBsFgc A1M2UyN4CKRAbTkVxoKq3ORVHXlDY6hPuCD8UATIzIfd/q0fdexue6Q8wMwOyQvp 6GuRc6WNCAXXaDdcUZzZFgteALiYG+GInLiisxP0THdb4vKQMn5XqV+PDFURAWmY SY9MZ7bUQcT/MXXxeEbF+Wj34dBDolwtkOcZ9FfCBgmXRyhUCKcYg/UAwMeg6qkN qGbtAGBRe4JzUqcKhJRLWu5Z6uoKhIcFdi8+cm3eM9cqZLcJIDAtxlbbVQDdUjZ1 X25QFzlgzMDu+b3reHus4TTWZ61GZ+ujzkgSAL0xrur5UvHpZLU= =ruIX -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
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Terence, On 1/7/20 7:33 PM, Terence M. Bandoian wrote: > On 1/7/2020 4:04 PM, Zahid Rahman wrote: >> Jerry Malcolm wrote : >> >>> Again this is the SAME line of code in java reading the >>> >SAME >> field in >>> the SAME database. Only thing different is >Linux/Windows OS >> >> >> >> On Tue, 7 Jan 2020, 21:52 , >> wrote: >> >>>> -Original Message- From: Jerry Malcolm >>>> Sent: Tuesday, January 07, 2020 3:14 >>>> PM To: users@tomcat.apache.org Subject: Re: Dates on Linux >>>> vs. Windows >>>> >>>> On 1/7/2020 3:09 PM, Michael Osipov wrote: >>>>> Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: >>>>>> This may be more of a Java question than Tomcat. But I'm >>>>>> not sure. I have the same code, talking to the same MySql >>>>>> Linux (AWS) database. I read a date column value in a >>>>>> Tomcat app. After calling resultSet.getDate(...) I >>>>>> printed the date instance and the getTime() value: >>>>>> >>>>>> On windows: 2019-02-01 154900080 >>>>>> >>>>>> On linux: 2019-01-31 154897920 >>>>>> >>>>>> Again this is the SAME line of code in java reading the >>>>>> SAME field in the SAME database. Only thing different is >>>>>> Linux/Windows OS. The date is supposed to be 2/1/2019 >>>>>> and shows that in phpMyAdmin. >>>>>> >>>>>> I've been running on Linux for a few months. But I don't >>>>>> have an extensive background in the specifics of Linux. >>>>>> I'm sure there must be something that is configured >>>>>> differently. I'm at a loss. But this is not a trivial >>>>>> problem. I do monthly billing. My dates need to be >>>>>> accurate. >>>>> Have you verified that you aren't tricked by any timezone >>>>> issues? >>>> Probably so. But how would I know? I was under the >>>> impression that java.sql.Date was timezone independent. >>>> Shouldn't it simply convert a month/day/year value to the >>>> number of milliseconds since the epoch? How would timezone >>>> issues affect that? And if I am 'tricked' how do I >>>> 'untrick'. What do I set/change? >>>>> >>> Those millisecond values are 6 hours apart, which looks like a >>> timezone issue. I happen to be in US Central time, which is 6 >>> hours earlier than UTC in winter. >>> >>> You're right that System.currentTimeMillis() itself is >>> independent of timezone but Date is not. > > As I understand it, for certain date/time column types, MySQL > subtracts the time zone from the value when written and adds it > back in when read. If your systems always use the same time zone > to read and write the data, it isn't a problem. But it can be if > the time zone varies. > > See https://dev.mysql.com/doc/refman/5.7/en/datetime.html Only for TIMESTAMP columns, which are fairly rarely used. Usually, you want a DATETIME field, which is a SQL standard. > The actual behavior is a little confusing, at least to me, because > I seem to remember variations in the storage of the date/time > columns while the documentation seems to indicate that date/time > values are not modified. Also, if I remember correctly, writing a > date/time value as a formatted string and then reading it back as a > string (e.g. ResultSet.getString) and parsing it circumvented the > time zone issue. I've never had the intestinal fortitude to change the time zone on a running MySQL server. So I'm not sure if it would shift every DATETIME value I've ever written to it. Yikes, I'm using local time. I hope I remember that for every server I ever migrate to. - -chris -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4VJrMACgkQHPApP6U8 pFgfyhAAu4jR5+hzu9oJJX9yN2rDeVw+3yGmHsiBF6eAZPUh7BLUkVV+kuhVxC+X pqWAvgStzyUAt/L7mdxNbNhyDSBMn5WJgjenQ70eLTqg+u3PYtkB/S+zxDgfwRbN x5WOzj/N1ypapdXCOZu7JnkmyL9tLQ1F+KfRIXPE9L6phlg4kDDvfvn9CGn+L/ir YkWtDk6YcNiWo4tguYj3lXNQ68CGBM2gkYKjWVtNKO1Keit8w/GgHbXnm9QmzEX8 jeXAL1LO4kUVEZayfaEmaLaSVfltR6ROB1Ubx4KNMG777wy9ln0odP1KSdWcmq+u Fu5kdsB25B+pxym0tPA21xIieGUp/4txCQn5WX66aLVS8CDgrWnl0uSP8iatZmPO wKL6i4qCEfPXYNTzB+CcLBuiK/8PXqcnp3YO2Xj2nqvh4pqqmkCUgJBM5umQVmhR 7iH3T1LjWhOPEBUx50Vz7+Dd7yj4Z6CZ/ubRYXTWXmUifu8hO2d1YehYj0ean+3r LaiTsmOHH9Tw1RDn+Wae4TCS+YEZZMLytSF2HnvXko87b/pU6eguapbO1ScBUepv KFhAUWUAzPtdvf2aZhveVscLCFGR1+Jl3Zv31qjSSriPuB3shRpWa2Q+g0/KPwrl zw3NiPsaIWL5hvxKdBllOKCMqoI7BwDlTkNocqb0QbHrYB5qcoA= =ETH9 -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
Summarizing what I know now... when I use the command line on the linux instance and do a mysql query, I get the correct date (i.e. the date that I set, the date I wanted, the date that mySQL exports to SQL file, and the date that appears in Windows tomcat). So this pretty much rules out the problem being in the mySQL server. I would think this also rules out the problem being in some timezone setting in the base Linux system hosting tomcat since I get the right date in the command line. That only leaves the JVM; JDBC package, and MySQL connector. MySQL RDS instance is set to US Central timezone. So it's going to return dates in central time, right? It appears that jdbc and/or the connector is assuming the db is gmt and knocking off another 6 hours. Is there some place in the datasource to tell tomcat the timezone of the database so it knows not to convert TZ when it doesn't need to? I can't find anything like that in the datasource documentation. - 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
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Jerry, On 1/7/20 7:03 PM, Jerry Malcolm wrote: > > On 1/7/2020 5:31 PM, calder wrote: >> On Tue, Jan 7, 2020, 17:17 Jerry Malcolm >> wrote: >> On Tue, 7 Jan 2020, 21:52 , wrote: >> '. What do I set/change? > Those millisecond values are 6 hours apart, which looks > like a timezone issue. I happen to be in US Central time, > which is 6 hours earlier than UTC in winter. > > You're right that System.currentTimeMillis() itself is > independent of timezone but Date is not. >>> That all makes sense. But at the end of the day, what do I do >>> to make it work right? I am also in Central time. My Linux OS >>> is set to central (at least I tried to set that. Afterwards my >>> log entries are correctly logging in central time instead of >>> gmt. So I assume it's set right). What do I need to do in >>> Tomcat to 'fix' it so that sql dates aren't somehow adjusted? >>> I simply want a 2019-02-01 in the database to appear as >>> 2019-02-01 in java. And the same code must work identically on >>> both OS's. >>> >> >> Have you checked the DST setting? > > I googled around trying to see how to check DST in Amazon Linux. > the Date command gave me the correct date and timezone with no DST > which is currently correct. I looked at the /etc/sysconfig/clock > file. It has two lines: > > ZONE="CST6ST" UTC=true > > But DST is only one hour change. An earlier post said that my two > different values of times were 6 hours off. Would DST be the cause > of that? Welcome to the wonderful world of civil date-reckoning. First off: you have the correct date. You just aren't holding it correctly[1]. Second, where are you showing this date? In a log somewhere? How are you printing it? If you are using java.sql.Date.toString(), then it should probably be telling you the TZ. If you are using SimpleDateFormat then you need to be aware that both java.util.Date (the surprising superclass of java.sql.Date, which doesn't have a time portion!) and SimpleDateFormat have their own TimeZone settings. And, for fun, there is no SimpleDateFormat constructor which takes a TimeZone argument. And, for more fun, java.util.Date doesn't have a TimeZone... it's got a (primitive) long offset in milliseconds. And a DST offset. Which is separate. Confused, yet? So you pretty much always need to do this: SimpleDateFormat df = new SimpleDateFormat("-MM-dd"); df.setTimeZone(TimeZone.getTimeZone("America/Chicago")); Date d = ... // whatever logger.trace("Got date: " + df.format(d)); If you aren't showing the time zone, you can always become confused. For example, if your Date object has an offset in UTC and your SimpleDateFormat is in US-CST, you can get different *days* depending upon the time of day represented by the Date object. It's a mess. The only way to do it is to always always ALWAYS handle the time zone properly. If you are using java.sql.Date which should be -MM-dd *only*, then you may have to truncate the time and/or adjust to e.g. UTC and maybe even do both of those so that you never accidentally cross the international date-line when interpreting your dates. But the good news is that only the humans will be confused by these dates. Generally speaking, the database and Java are doing things correctly. Unless you are accepting input. Whenever you ask a user to enter a date (or, worse, a date-time), you need to read their input *in their timezone*, since, well, that's how they think. It's 19:38 local time for me, but for someone in London it's 01:38 *tomorrow*. So if I ask them for the current datetime, they will say 2020-01-08T01:38:00. If I don't interpret that as being in UK Winter Time, then I might think that the timestamp represents 2020-01-08T01:38:00 CST. Or maybe whatever the default time zone is for your EC2 instance. Or your JVM. Or for the user who actually launched your JVM. This time. So do yourself a favor and fix all your date-manipulation code so it's always doing the right thing. It may take a while, but it will be Correc t. Everybody has to go through this at some point. Good luck. Oh, and just so you don't feel like you bet on the wrong horse, this isn't just a Java thing. It's the same with all languages. Java was written in a quaint time when they thought that java.util.Date would do everything necessary. Until java.util.Calendar came along. And then Jodatime. And then java.time.*. If you look at most other languages, there are similar progressions with vestigial leftovers that still work, and are littered all over all the APIs. - -chris [1] https://knowyourmeme.com/memes/events/iphone-4-death-grip -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/ iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4VJZ4ACgkQHPApP6U8 pFgnIQ//S3S/K34K80fQN4nhgzdFBlVEcko+tiDBD5GR2e3Tt0BJ36nF6v9D+p3o Y37JRaMiU4T2SaMWXREs2rNID+UmZXsfB3Cfgvub+vFqGZiXeEUm2KMv8KS1NYER
Re: Dates on Linux vs. Windows
On 1/7/2020 4:04 PM, Zahid Rahman wrote: Jerry Malcolm wrote : >Again this is the SAME line of code in java reading the >SAME field in the SAME database. Only thing different is >Linux/Windows OS On Tue, 7 Jan 2020, 21:52 , wrote: -Original Message- From: Jerry Malcolm Sent: Tuesday, January 07, 2020 3:14 PM To: users@tomcat.apache.org Subject: Re: Dates on Linux vs. Windows On 1/7/2020 3:09 PM, Michael Osipov wrote: Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: This may be more of a Java question than Tomcat. But I'm not sure. I have the same code, talking to the same MySql Linux (AWS) database. I read a date column value in a Tomcat app. After calling resultSet.getDate(...) I printed the date instance and the getTime() value: On windows: 2019-02-01 154900080 On linux: 2019-01-31 154897920 Again this is the SAME line of code in java reading the SAME field in the SAME database. Only thing different is Linux/Windows OS. The date is supposed to be 2/1/2019 and shows that in phpMyAdmin. I've been running on Linux for a few months. But I don't have an extensive background in the specifics of Linux. I'm sure there must be something that is configured differently. I'm at a loss. But this is not a trivial problem. I do monthly billing. My dates need to be accurate. Have you verified that you aren't tricked by any timezone issues? Probably so. But how would I know? I was under the impression that java.sql.Date was timezone independent. Shouldn't it simply convert a month/day/year value to the number of milliseconds since the epoch? How would timezone issues affect that? And if I am 'tricked' how do I 'untrick'. What do I set/change? Those millisecond values are 6 hours apart, which looks like a timezone issue. I happen to be in US Central time, which is 6 hours earlier than UTC in winter. You're right that System.currentTimeMillis() itself is independent of timezone but Date is not. As I understand it, for certain date/time column types, MySQL subtracts the time zone from the value when written and adds it back in when read. If your systems always use the same time zone to read and write the data, it isn't a problem. But it can be if the time zone varies. See https://dev.mysql.com/doc/refman/5.7/en/datetime.html The actual behavior is a little confusing, at least to me, because I seem to remember variations in the storage of the date/time columns while the documentation seems to indicate that date/time values are not modified. Also, if I remember correctly, writing a date/time value as a formatted string and then reading it back as a string (e.g. ResultSet.getString) and parsing it circumvented the time zone issue. Hope that helps. -Terence Bandoian - 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
On 1/7/2020 5:31 PM, calder wrote: On Tue, Jan 7, 2020, 17:17 Jerry Malcolm wrote: On Tue, 7 Jan 2020, 21:52 , wrote: '. What do I set/change? Those millisecond values are 6 hours apart, which looks like a timezone issue. I happen to be in US Central time, which is 6 hours earlier than UTC in winter. You're right that System.currentTimeMillis() itself is independent of timezone but Date is not. That all makes sense. But at the end of the day, what do I do to make it work right? I am also in Central time. My Linux OS is set to central (at least I tried to set that. Afterwards my log entries are correctly logging in central time instead of gmt. So I assume it's set right). What do I need to do in Tomcat to 'fix' it so that sql dates aren't somehow adjusted? I simply want a 2019-02-01 in the database to appear as 2019-02-01 in java. And the same code must work identically on both OS's. Have you checked the DST setting? I googled around trying to see how to check DST in Amazon Linux. the Date command gave me the correct date and timezone with no DST which is currently correct. I looked at the /etc/sysconfig/clock file. It has two lines: ZONE="CST6ST" UTC=true But DST is only one hour change. An earlier post said that my two different values of times were 6 hours off. Would DST be the cause of that? - 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
On Tue, Jan 7, 2020, 17:17 Jerry Malcolm wrote: > > > On Tue, 7 Jan 2020, 21:52 , wrote: > '. What do I set/change? > > >> Those millisecond values are 6 hours apart, which looks like a timezone > >> issue. I happen to be in US Central time, which is 6 hours earlier than > >> UTC in winter. > >> > >> You're right that System.currentTimeMillis() itself is independent of > >> timezone but Date is not. > > That all makes sense. But at the end of the day, what do I do to make > it work right? I am also in Central time. My Linux OS is set to > central (at least I tried to set that. Afterwards my log entries are > correctly logging in central time instead of gmt. So I assume it's set > right). What do I need to do in Tomcat to 'fix' it so that sql dates > aren't somehow adjusted? I simply want a 2019-02-01 in the database to > appear as 2019-02-01 in java. And the same code must work identically > on both OS's. > Have you checked the DST setting?
Re: Dates on Linux vs. Windows
On 1/7/2020 4:04 PM, Zahid Rahman wrote: Jerry Malcolm wrote : >Again this is the SAME line of code in java reading the >SAME field in the SAME database. Only thing different is >Linux/Windows OS On Tue, 7 Jan 2020, 21:52 , wrote: -Original Message- From: Jerry Malcolm Sent: Tuesday, January 07, 2020 3:14 PM To: users@tomcat.apache.org Subject: Re: Dates on Linux vs. Windows On 1/7/2020 3:09 PM, Michael Osipov wrote: Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: This may be more of a Java question than Tomcat. But I'm not sure. I have the same code, talking to the same MySql Linux (AWS) database. I read a date column value in a Tomcat app. After calling resultSet.getDate(...) I printed the date instance and the getTime() value: On windows: 2019-02-01 154900080 On linux: 2019-01-31 154897920 Again this is the SAME line of code in java reading the SAME field in the SAME database. Only thing different is Linux/Windows OS. The date is supposed to be 2/1/2019 and shows that in phpMyAdmin. I've been running on Linux for a few months. But I don't have an extensive background in the specifics of Linux. I'm sure there must be something that is configured differently. I'm at a loss. But this is not a trivial problem. I do monthly billing. My dates need to be accurate. Have you verified that you aren't tricked by any timezone issues? Probably so. But how would I know? I was under the impression that java.sql.Date was timezone independent. Shouldn't it simply convert a month/day/year value to the number of milliseconds since the epoch? How would timezone issues affect that? And if I am 'tricked' how do I 'untrick'. What do I set/change? Those millisecond values are 6 hours apart, which looks like a timezone issue. I happen to be in US Central time, which is 6 hours earlier than UTC in winter. You're right that System.currentTimeMillis() itself is independent of timezone but Date is not. That all makes sense. But at the end of the day, what do I do to make it work right? I am also in Central time. My Linux OS is set to central (at least I tried to set that. Afterwards my log entries are correctly logging in central time instead of gmt. So I assume it's set right). What do I need to do in Tomcat to 'fix' it so that sql dates aren't somehow adjusted? I simply want a 2019-02-01 in the database to appear as 2019-02-01 in java. And the same code must work identically on both OS's. Thanks - 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
Jerry Malcolm wrote : >Again this is the SAME line of code in java reading the >SAME field in > the SAME database. Only thing different is >Linux/Windows OS On Tue, 7 Jan 2020, 21:52 , wrote: > > > -Original Message- > > From: Jerry Malcolm > > Sent: Tuesday, January 07, 2020 3:14 PM > > To: users@tomcat.apache.org > > Subject: Re: Dates on Linux vs. Windows > > > > On 1/7/2020 3:09 PM, Michael Osipov wrote: > > > Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: > > >> This may be more of a Java question than Tomcat. But I'm not sure. I > > >> have the same code, talking to the same MySql Linux (AWS) database. > > >> I read a date column value in a Tomcat app. After calling > > >> resultSet.getDate(...) I printed the date instance and the getTime() > > >> value: > > >> > > >> On windows: 2019-02-01 154900080 > > >> > > >> On linux: 2019-01-31 154897920 > > >> > > >> Again this is the SAME line of code in java reading the SAME field in > > >> the SAME database. Only thing different is Linux/Windows OS. The > > >> date is supposed to be 2/1/2019 and shows that in phpMyAdmin. > > >> > > >> I've been running on Linux for a few months. But I don't have an > > >> extensive background in the specifics of Linux. I'm sure there must > > >> be something that is configured differently. I'm at a loss. But this > > >> is not a trivial problem. I do monthly billing. My dates need to be > > >> accurate. > > > > > > Have you verified that you aren't tricked by any timezone issues? > > Probably so. But how would I know? I was under the impression that > > java.sql.Date was timezone independent. Shouldn't it simply convert a > > month/day/year value to the number of milliseconds since the epoch? How > > would timezone issues affect that? And if I am 'tricked' how do I > > 'untrick'. What do I set/change? > > > > > > > > Those millisecond values are 6 hours apart, which looks like a timezone > issue. I happen to be in US Central time, which is 6 hours earlier than > UTC in winter. > > You're right that System.currentTimeMillis() itself is independent of > timezone but Date is not. > > > >
RE: Dates on Linux vs. Windows
> -Original Message- > From: Jerry Malcolm > Sent: Tuesday, January 07, 2020 3:14 PM > To: users@tomcat.apache.org > Subject: Re: Dates on Linux vs. Windows > > On 1/7/2020 3:09 PM, Michael Osipov wrote: > > Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: > >> This may be more of a Java question than Tomcat. But I'm not sure. I > >> have the same code, talking to the same MySql Linux (AWS) database. > >> I read a date column value in a Tomcat app. After calling > >> resultSet.getDate(...) I printed the date instance and the getTime() > >> value: > >> > >> On windows: 2019-02-01 154900080 > >> > >> On linux: 2019-01-31 154897920 > >> > >> Again this is the SAME line of code in java reading the SAME field in > >> the SAME database. Only thing different is Linux/Windows OS. The > >> date is supposed to be 2/1/2019 and shows that in phpMyAdmin. > >> > >> I've been running on Linux for a few months. But I don't have an > >> extensive background in the specifics of Linux. I'm sure there must > >> be something that is configured differently. I'm at a loss. But this > >> is not a trivial problem. I do monthly billing. My dates need to be > >> accurate. > > > > Have you verified that you aren't tricked by any timezone issues? > Probably so. But how would I know? I was under the impression that > java.sql.Date was timezone independent. Shouldn't it simply convert a > month/day/year value to the number of milliseconds since the epoch? How > would timezone issues affect that? And if I am 'tricked' how do I > 'untrick'. What do I set/change? > > > > Those millisecond values are 6 hours apart, which looks like a timezone issue. I happen to be in US Central time, which is 6 hours earlier than UTC in winter. You're right that System.currentTimeMillis() itself is independent of timezone but Date is not.
Re: Dates on Linux vs. Windows
If you wish to find out if the database connection API is buggy. Is the result when you use select query from each of the operating system same. Select column_name from table; If select on both return values are same then likely the database API is buggy. You have choice of two database connection APIs. One API is tomcat specific. The other is vendor neutral. On Tue, 7 Jan 2020, 21:09 Michael Osipov, wrote: > Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: > > This may be more of a Java question than Tomcat. But I'm not sure. I > > have the same code, talking to the same MySql Linux (AWS) database. I > > read a date column value in a Tomcat app. After calling > > resultSet.getDate(...) I printed the date instance and the getTime() > value: > > > > On windows: 2019-02-01 154900080 > > > > On linux: 2019-01-31 154897920 > > > > Again this is the SAME line of code in java reading the SAME field in > > the SAME database. Only thing different is Linux/Windows OS. The date > > is supposed to be 2/1/2019 and shows that in phpMyAdmin. > > > > I've been running on Linux for a few months. But I don't have an > > extensive background in the specifics of Linux. I'm sure there must be > > something that is configured differently. I'm at a loss. But this is > > not a trivial problem. I do monthly billing. My dates need to be > accurate. > > Have you verified that you aren't tricked by any timezone issues? > > > - > 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
On 1/7/2020 3:09 PM, Michael Osipov wrote: Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: This may be more of a Java question than Tomcat. But I'm not sure. I have the same code, talking to the same MySql Linux (AWS) database. I read a date column value in a Tomcat app. After calling resultSet.getDate(...) I printed the date instance and the getTime() value: On windows: 2019-02-01 154900080 On linux: 2019-01-31 154897920 Again this is the SAME line of code in java reading the SAME field in the SAME database. Only thing different is Linux/Windows OS. The date is supposed to be 2/1/2019 and shows that in phpMyAdmin. I've been running on Linux for a few months. But I don't have an extensive background in the specifics of Linux. I'm sure there must be something that is configured differently. I'm at a loss. But this is not a trivial problem. I do monthly billing. My dates need to be accurate. Have you verified that you aren't tricked by any timezone issues? Probably so. But how would I know? I was under the impression that java.sql.Date was timezone independent. Shouldn't it simply convert a month/day/year value to the number of milliseconds since the epoch? How would timezone issues affect that? And if I am 'tricked' how do I 'untrick'. What do I set/change? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - 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
Am 07.01.20 um 21:58 schrieb Jerry Malcolm: > This may be more of a Java question than Tomcat. But I'm not sure. I > have the same code, talking to the same MySql Linux (AWS) database. I > read a date column value in a Tomcat app. After calling > resultSet.getDate(...) I printed the date instance and the getTime() > value: > > On windows: 2019-02-01 154900080 > > On linux: 2019-01-31 154897920 > > Again this is the SAME line of code in java reading the SAME field in > the SAME database. Only thing different is Linux/Windows OS. The > date is supposed to be 2/1/2019 and shows that in phpMyAdmin. > > I've been running on Linux for a few months. But I don't have an > extensive background in the specifics of Linux. I'm sure there must > be something that is configured differently. I'm at a loss. But this > is not a trivial problem. I do monthly billing. My dates need to be > accurate. > > What am I doing wrong? (BTW Tomcat 8.5.x and Java 1.8.0_222 running on > AWS Linux, not AWS Linux 2). Maybe different timezone settings on the clients that propagate to the database? Have you looked at setting/reading the timezones in mysql (and after that on the clients) like https://stackoverflow.com/questions/930900/how-do-i-set-the-time-zone-of-mysql On linux a simple "date" command will print out the currently used timezone. For me it prints: $ date Di 7. Jan 22:06:37 CET 2020 or without a language setting: $ LANG= date Tue Jan 7 22:12:05 CET 2020 Felix > > Thanks. > > Jerry > > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > - 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
Am 2020-01-07 um 21:58 schrieb Jerry Malcolm: This may be more of a Java question than Tomcat. But I'm not sure. I have the same code, talking to the same MySql Linux (AWS) database. I read a date column value in a Tomcat app. After calling resultSet.getDate(...) I printed the date instance and the getTime() value: On windows: 2019-02-01 154900080 On linux: 2019-01-31 154897920 Again this is the SAME line of code in java reading the SAME field in the SAME database. Only thing different is Linux/Windows OS. The date is supposed to be 2/1/2019 and shows that in phpMyAdmin. I've been running on Linux for a few months. But I don't have an extensive background in the specifics of Linux. I'm sure there must be something that is configured differently. I'm at a loss. But this is not a trivial problem. I do monthly billing. My dates need to be accurate. Have you verified that you aren't tricked by any timezone issues? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Dates on Linux vs. Windows
This may be more of a Java question than Tomcat. But I'm not sure. I have the same code, talking to the same MySql Linux (AWS) database. I read a date column value in a Tomcat app. After calling resultSet.getDate(...) I printed the date instance and the getTime() value: On windows: 2019-02-01 154900080 On linux: 2019-01-31 154897920 Again this is the SAME line of code in java reading the SAME field in the SAME database. Only thing different is Linux/Windows OS. The date is supposed to be 2/1/2019 and shows that in phpMyAdmin. I've been running on Linux for a few months. But I don't have an extensive background in the specifics of Linux. I'm sure there must be something that is configured differently. I'm at a loss. But this is not a trivial problem. I do monthly billing. My dates need to be accurate. What am I doing wrong? (BTW Tomcat 8.5.x and Java 1.8.0_222 running on AWS Linux, not AWS Linux 2). Thanks. Jerry - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org