MySQL select datetime without Daylight saving time change

Question

I have a table where I store data from the sensors

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 date 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 query from my java app, it returns the following date 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 DST change in the UK which happened on 26th March at 1am. I think JDBC connection is changing the time to BST but I don't want that.

I have tried to set the MySQL server global time zone to 00:00

SET @@global.time_zone='+00:00';

But no success! How can I get the correct time without the DST change?

EDIT: I am using Java 1.7 and mysql-connector-java-5.1.19-bin.jar

I was getting data as strings before but after Mark Rotteveel comments I tried PreparedStaement with the calendar object set to UTC but both returning 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 another strange thing that when I change the default timezone to UTC it returns repeating 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

See http://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific/14070771#14070771 — Mark Rotteveel, Mar 28 '17 at 14:20
@MarkRotteveel In this question the data type is timestamp but my table has a datatype of datetime, I was under the impression that only timestamp stores the timezone value and datetime does not store this information — Ana, Mar 28 '17 at 14:27
Don't confuse JDBC data types with MySQL data types. A `Types.TIMESTAMP` (or `java.sql.Timestamp`) has no time zone, but when retrieving a value from the database for a datatype that has no time zone information (like MySQLs `datetime`), then JDBC requires that it is interpreted in the current JVM timezone, unless you use the `getTimestamp` that takes a `Calendar` object with time zone information. — Mark Rotteveel, Mar 28 '17 at 14:52
Please [edit] your question to indicate the version of Java you are running and the version of the MySQL JDBC connector that you are using. — Gord Thompson, Mar 31 '17 at 16:25
Please share the Java code you use for producing the output. — Mick Mnemonic, Mar 31 '17 at 18:14
Try adding `useLegacyDatetimeCode=false` to your connection string and see if that helps. — Gord Thompson, Apr 03 '17 at 12:24
@GordThompson still same result, I have also tried serverTimezone=UTC in the connection string, no change — Ana, Apr 03 '17 at 13:16

Gord Thompson · Accepted Answer · 2017-04-05T17:23:47.267

There are two issues at play here:

Are we getting the desired java.sql.Timestamp values back from the server?
What are we actually seeing when we ask Java to display the Timestamp value as a date/time string?

To help sort this out we can display the Timestamp value in its numeric form. Timestamp#getTime() returns the number of milliseconds since the epoch (1970-01-01 00:00:00 UTC), but we can make it a bit easier to read if we divide that by 60000 to give the minutes since the epoch.

We can also use SimpleDateFormat objects to format the Timestamp values in an unambiguous way.

So for a sample 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 wasn't getting the desired Timestamp values back since both rows 2 and 3 show "24841510 minutes since epoch".

I was able to correct that by simply adding useLegacyDatetimeCode=false to my 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 row 3 now shows "24841570 minutes since epoch", 60 minutes later than row 2. Notice also that the UTC-formatted datetime value matches the string representation we got by using CAST(dateTime AS CHAR) in the SQL query.

Thanks Gord... adding `useLegacyDatetimeCode=false` in the connection string solved the issue and your example helped. I have one more question, Does java treat both (`timestamp` and `datetime`) MySQL data types as timestamp? Because in my database table, the data type is datetime but when I get the data in ResultSet object, its class is timestamp (`resultSet.getObject(1).getClass().getName() = java.sql.timestamp`) — Ana, Apr 05 '17 at 10:13
By default, MySQL Connector/J will return values from both `DATETIME` columns and `TIMESTAMP` columns as `java.sql.Timestamp` objects. However, it does not change the behaviour of those column types in the MySQL database. The fact that MySQL `TIMESTAMP` columns and `java.sql.Timestamp` objects share the same "timestamp" name is really just coincidence. — Gord Thompson, Apr 05 '17 at 17:10

MySQL select datetime without Daylight saving time change

1 Answers1