MySQL picks date and time without changing daylight saving time
I have a table where sensor data is stored
CREATE TABLE `testdatabase` (
`dateTime` datetime DEFAULT NULL,
`data` varchar(200) DEFAULT NULL,
`sensorID` varchar(10) DEFAULT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6918 DEFAULT CHARSET=utf8
This is the select query I am running,
SELECT `dateTime` from testdatabase WHERE sensorID='ABC1234' AND (dateTime BETWEEN '2017-03-26 00:01:00' AND '2017-03-26 00:00:00') order by `dateTime` asc;
In MySQL workbench, when I run this query, it returns the correct time.
2017-03-26 00:10:00
2017-03-26 00:20:00
2017-03-26 00:30:00
2017-03-26 00:40:00
2017-03-26 00:50:00
2017-03-26 01:00:00
2017-03-26 01:10:00
2017-03-26 01:20:00
2017-03-26 01:30:00
2017-03-26 01:40:00
2017-03-26 01:50:00
2017-03-26 02:00:00
2017-03-26 02:10:00
2017-03-26 02:20:00
2017-03-26 02:30:00
2017-03-26 02:40:00
2017-03-26 02:50:00
2017-03-26 03:00:00
But when I run this request from my java application, it returns the following time.
2017-03-26 00:10:00
2017-03-26 00:20:00
2017-03-26 00:30:00
2017-03-26 00:40:00
2017-03-26 00:50:00
2017-03-26 02:00:00
2017-03-26 02:10:00
2017-03-26 02:20:00
2017-03-26 02:30:00
2017-03-26 02:40:00
2017-03-26 02:50:00
2017-03-26 02:00:00
2017-03-26 02:10:00
2017-03-26 02:20:00
2017-03-26 02:30:00
2017-03-26 02:40:00
2017-03-26 02:50:00
2017-03-26 03:00:00
Obviously this has something to do with the UK DST change that happened on March 26 at 1:00 am. I think the JDBC connection changes the time to BST, but I don't want that.
I tried to set the global timezone of the MySQL server to 00:00
SET @@global.time_zone='+00:00';
But no success! How can I get the correct time without changing the DST?
EDIT: I am using Java 1.7
andmysql-connector-java-5.1.19-bin.jar
I was getting data as strings before, but after Mark Rotteveel
comments, I tried PreparedStaement
with a calendar object set to UTC, but both returned the same results.
try
{
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);
while(resultSet.next())
{
System.out.println("Without Calendar Object : "+resultSet.getString(1));
}
}catch (SQLException e){e.printStackTrace();}
try
{
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
while(resultSet.next())
{
System.out.println("With Calendar Object : "+resultSet.getTimestamp(1, cal));
}
}catch (SQLException e){e.printStackTrace();}
OUTPUT:
Without Calendar Object : 2017-03-26 00:10:00.0
Without Calendar Object : 2017-03-26 00:20:00.0
Without Calendar Object : 2017-03-26 00:30:00.0
Without Calendar Object : 2017-03-26 00:40:00.0
Without Calendar Object : 2017-03-26 00:50:00.0
Without Calendar Object : 2017-03-26 02:00:00.0
Without Calendar Object : 2017-03-26 02:10:00.0
Without Calendar Object : 2017-03-26 02:20:00.0
Without Calendar Object : 2017-03-26 02:30:00.0
Without Calendar Object : 2017-03-26 02:40:00.0
Without Calendar Object : 2017-03-26 02:50:00.0
Without Calendar Object : 2017-03-26 02:00:00.0
Without Calendar Object : 2017-03-26 02:10:00.0
Without Calendar Object : 2017-03-26 02:20:00.0
Without Calendar Object : 2017-03-26 02:30:00.0
Without Calendar Object : 2017-03-26 02:40:00.0
Without Calendar Object : 2017-03-26 02:50:00.0
Without Calendar Object : 2017-03-26 03:00:00.0
With Calendar Object : 2017-03-26 00:10:00.0
With Calendar Object : 2017-03-26 00:20:00.0
With Calendar Object : 2017-03-26 00:30:00.0
With Calendar Object : 2017-03-26 00:40:00.0
With Calendar Object : 2017-03-26 00:50:00.0
With Calendar Object : 2017-03-26 02:00:00.0
With Calendar Object : 2017-03-26 02:10:00.0
With Calendar Object : 2017-03-26 02:20:00.0
With Calendar Object : 2017-03-26 02:30:00.0
With Calendar Object : 2017-03-26 02:40:00.0
With Calendar Object : 2017-03-26 02:50:00.0
With Calendar Object : 2017-03-26 02:00:00.0
With Calendar Object : 2017-03-26 02:10:00.0
With Calendar Object : 2017-03-26 02:20:00.0
With Calendar Object : 2017-03-26 02:30:00.0
With Calendar Object : 2017-03-26 02:40:00.0
With Calendar Object : 2017-03-26 02:50:00.0
With Calendar Object : 2017-03-26 03:00:00.0
EDIT 2: There is one more strange thing: when I change the default timezone to UTC, it returns duplicate values ββfor 1am instead of 2am.
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
OUTPUT:
Without Calendar Object : 2017-03-26 00:10:00.0
Without Calendar Object : 2017-03-26 00:20:00.0
Without Calendar Object : 2017-03-26 00:30:00.0
Without Calendar Object : 2017-03-26 00:40:00.0
Without Calendar Object : 2017-03-26 00:50:00.0
Without Calendar Object : 2017-03-26 01:00:00.0
Without Calendar Object : 2017-03-26 01:10:00.0
Without Calendar Object : 2017-03-26 01:20:00.0
Without Calendar Object : 2017-03-26 01:30:00.0
Without Calendar Object : 2017-03-26 01:40:00.0
Without Calendar Object : 2017-03-26 01:50:00.0
Without Calendar Object : 2017-03-26 01:00:00.0
Without Calendar Object : 2017-03-26 01:10:00.0
Without Calendar Object : 2017-03-26 01:20:00.0
Without Calendar Object : 2017-03-26 01:30:00.0
Without Calendar Object : 2017-03-26 01:40:00.0
Without Calendar Object : 2017-03-26 01:50:00.0
Without Calendar Object : 2017-03-26 02:00:00.0
With Calendar Object : 2017-03-26 00:10:00.0
With Calendar Object : 2017-03-26 00:20:00.0
With Calendar Object : 2017-03-26 00:30:00.0
With Calendar Object : 2017-03-26 00:40:00.0
With Calendar Object : 2017-03-26 00:50:00.0
With Calendar Object : 2017-03-26 01:00:00.0
With Calendar Object : 2017-03-26 01:10:00.0
With Calendar Object : 2017-03-26 01:20:00.0
With Calendar Object : 2017-03-26 01:30:00.0
With Calendar Object : 2017-03-26 01:40:00.0
With Calendar Object : 2017-03-26 01:50:00.0
With Calendar Object : 2017-03-26 01:00:00.0
With Calendar Object : 2017-03-26 01:10:00.0
With Calendar Object : 2017-03-26 01:20:00.0
With Calendar Object : 2017-03-26 01:30:00.0
With Calendar Object : 2017-03-26 01:40:00.0
With Calendar Object : 2017-03-26 01:50:00.0
With Calendar Object : 2017-03-26 02:00:00.0
source to share
There are two problems here:
- Are we getting the desired values
java.sql.Timestamp
from the server? - What do we see when we ask Java to display a value
Timestamp
as a date / time string?
To help figure this out, we can display the value Timestamp
in its numeric form. Timestamp#getTime()
returns the number of milliseconds since epoch (1970-01-01 00:00:00 UTC), but we can make it a little easier to read if we divide it by 60,000 to give minutes since epoch.
We can also use objects SimpleDateFormat
to format values ββin an Timestamp
unambiguous way.
So, for an example table named tztest
that looks like this in phpMyAdmin
id dateTime -- ------------------- 1 2017-03-26 00:10:00 2 2017-03-26 01:10:00 3 2017-03-26 02:10:00
when i ran the following java code
connectionUrl = "jdbc:mysql://localhost:3307/mydb";
try (Connection conn = DriverManager.getConnection(connectionUrl, myUid, myPwd)) {
SimpleDateFormat sdfLocal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
Calendar calUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
SimpleDateFormat sdfUTC = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
sdfUTC.setCalendar(calUTC);
String sql =
"SELECT id, CAST(dateTime AS CHAR) AS datetimeString, dateTime " +
"FROM tztest ORDER BY dateTime";
try ( Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
int id = rs.getInt("id");
String datetimeString = rs.getString("datetimeString");
Timestamp timestampValue = rs.getTimestamp("dateTime", calUTC);
long minutesSinceEpoch = timestampValue.getTime() / 60000;
System.out.printf("%d: %s -> %d minutes since epoch -> %s / %s%n",
id,
datetimeString,
minutesSinceEpoch,
sdfLocal.format(timestampValue),
sdfUTC.format(timestampValue)
);
}
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
the console output was
1: 2017-03-26 00:10:00 -> 24841450 minutes since epoch -> 2017-03-26 00:10:00 GMT / 2017-03-26 00:10:00 UTC 2: 2017-03-26 01:10:00 -> 24841510 minutes since epoch -> 2017-03-26 02:10:00 BST / 2017-03-26 01:10:00 UTC 3: 2017-03-26 02:10:00 -> 24841510 minutes since epoch -> 2017-03-26 02:10:00 BST / 2017-03-26 01:10:00 UTC
Clearly I was not getting the values ββI wanted Timestamp
as both lines 2 and 3 show "24841510 minutes from epoch".
I was able to fix this by simply adding useLegacyDatetimeCode=false
to the connection string
connectionUrl = "jdbc:mysql://localhost:3307/mydb?useLegacyDatetimeCode=false";
after which the console output was
1: 2017-03-26 00:10:00 -> 24841450 minutes since epoch -> 2017-03-26 00:10:00 GMT / 2017-03-26 00:10:00 UTC 2: 2017-03-26 01:10:00 -> 24841510 minutes since epoch -> 2017-03-26 02:10:00 BST / 2017-03-26 01:10:00 UTC 3: 2017-03-26 02:10:00 -> 24841570 minutes since epoch -> 2017-03-26 03:10:00 BST / 2017-03-26 02:10:00 UTC
Notice that line 3 now shows "24841570 minutes from epoch", 60 minutes after line 2. Notice that the UTC date / time value corresponds to the string representation we got using CAST(dateTime AS CHAR)
in the SQL query.
source to share