-----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("yyyy-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 = null;
System.out.println("===== SELECT explicit date with
SYSTEM time zone ====");
s.executeUpdate("SET time_zone = 'SYSTEM'");
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 = null;
s.close(); s = null;
} finally {
if(null != rs) try { rs.close(); } catch (SQLException sqle)
{
System.err.println("Cannot close JDBC ResultSet");
sqle.printStackTrace();
}
if(null != s) try { s.close(); } catch (SQLException sqle) {
System.err.println("Cannot close JDBC Statement");
sqle.printStackTrace();
}
if(null != conn) try { conn.close(); } catch (SQLException
sqle) {
System.err.println("Cannot close JDBC Connection");
sqle.printStackTrace();
}
}
}
@SuppressWarnings("deprecation")
private static void dump(Date date)
{
System.out.println(date.getClass().getName());
System.out.print(" toString()=");
System.out.println(date.toString());
System.out.print(" t=");
System.out.println(date.getTime());
System.out.print(" year=");
System.out.println(date.getYear() + 1900);
System.out.print(" month=");
System.out.println(date.getMonth() + 1);
System.out.print(" day=");
System.out.println(date.getDay());
// java.sql.Date.getHours, etc. throw IllegalArgumentException
if called
if(!(date instanceof java.sql.Date)) {
System.out.print(" hour=");
System.out.println(date.getHours());
System.out.print(" minute=");
System.out.println(date.getMinutes());
System.out.print(" second=");
System.out.println(date.getSeconds());
}
System.out.print(" offset=");
System.out.println(date.getTimezoneOffset());
System.out.print(" df=");
System.out.println(df.format(date));
}
}
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl4XfngACgkQHPApP6U8
pFiTvw//eNPIiNKUsDjaQhMc+1VRbXFUCMjrvkq8QpqssLXBuvTTx0iSMKSsZTbq
+FzBiG7u8jT7i3M66hLyqSUkgExsVDGaZv0sYs4wJlWso6Fc+5CMpFOf5ZZKYOSK
iDRRbLUaOMaocW9ldKK6CkSHSvPMDIOgOA+EAU9QKTKqgvosVZ+3qUl5qmeUJIQn
okM8L6QxmrCp90vXfm8yy3j2C2Hi7v15ayeVcETQ5CTPNK358in97y/9L9Np2xS4
bNkI+5u6uhfPpz8EtLpd5vdfzj7+U1YrncpqB2BZtDYYfnQdmC5x0GQEuX7GgBZ/
25lw4VkU4WQ/KuAdOUZaIh6QyAp7Nc6fLXZFxDJhS9XNN8lOKYSO8dvEfNSGLKpG
BFZ+eymhLsFN7QhJDxgzefnkFYLC8y57AdXYosByg4lVsrpTFt4M1aJ08niTQciu
eV2Di86U9SZsoJua0qkj4VJnC+jtBRzm/2gfw5fAfMf19gbaURKZieaF/jsZx+td
R7ticX1bl/tzVkp/7WJvlVe0sclAmV08fdXEh+H5E5N44urBgPf+N3ZknIx4QuAN
7oHUu8vW258ENWcWCjHKJ5a+qajnRukJthn8RElU5djLku2NuWwU803iHPwt6A4e
QPXNan43Nx8D+tEQa54B6txdie6jk/6k53XdHhjEq+JiiNP1bM8=
=p39n
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
