Clever Geek Handbook

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
+3


source to share


1 answer


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.

+1


source






More articles:


All Articles