Inserting date / time in a specific zone into MySQL database

Question

I found no way to insert date/time into MySQL database in a standard zone like UTC.

The following segment of code authenticates a user by using Apache DBCP.

public final class AuthenticationDAO {

    public boolean isAuthenticated(String userName, String password) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        boolean status = false;

        try {
            connection = DatabaseConnection.getConnection();
            preparedStatement = connection.prepareStatement("select * from admin_tbl where lower(admin_id)=lower(?) and BINARY password=?");
            preparedStatement.setString(1, userName);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            status = resultSet.next();

            if (status) {
                preparedStatement = connection.prepareStatement("update admin_tbl set last_login=? where lower(admin_id)=lower(?) and BINARY password=?");
                preparedStatement.setTimestamp(1, new Timestamp(new Date().getTime())); // java.util.Date / java.sql.Timestamp
                preparedStatement.setString(2, userName);
                preparedStatement.setString(3, password);
                preparedStatement.executeUpdate();
            }
        } finally {
            if (connection != null) {connection.close();}
            if (resultSet != null) {resultSet.close();}
            if (preparedStatement != null) {preparedStatement.close();}
        }
        return status;
    }
}

Only one line of this code will be of our interest.

preparedStatement.setTimestamp(1, new Timestamp(new Date().getTime()));

This line updates the login date/time, when a login attempt by a user/admin is successful. The column last_login in MySQL is of type DATETIME.

I want to insert date/time in UTC zone in MySQL that doesn't happen. It appears that it takes a local zone.

By any means, is it possible to insert date/time into MySQL in UTC zone? Any data type in Java and/or MySQL may be suggested - not only which I'm presenting here.

score 3 · Answer 1 · answered Oct 16 '13 at 22:11

You can set the timezone for your mySQL data in two places: global config and session config. You cannot specify to mysql 'this date is GMT, but this date is EST'. You have to convert them to a common timezone.

Best practice is to set your server to a particular timezone [eg: GMT] and ensure that you are converting your date/times accordingly during storage. Realistically, all of your back-end data should be using a single timezone, and any conversion should take place immediately preceding output.

score 2 · Accepted Answer · edited May 23 '17 at 11:59

Date is timezone agnostic in Java. It always takes UTC (by default and always). Therefore, Date or Timestamp itself is not the cause but when Date / Timestamp is passed through a JDBC driver to a database, it interprets date / time according to the JVM time zone which defaults to the system time zone in turn (the native operating system zone).

Therefore, unless the MySQL JDBC driver was explicitly forced to use the UTC zone or JVM itself is set to use that zone, it would not store Date / Timestamp into the target database using UTC even though MySQL itself were to be configured to use UTC using default_time_zone='+00:00' in my.ini or my.cnf in the [mysqld] section. Some databases like Oracle may support time stamp with time zone and it may be an exception which I am not familiar with (untested as I do not have that environment at present).

void setTimestamp(int parameterIndex, Timestamp x, Calendar cal) throws SQLException

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

_{The same thing is also mentioned for other related getter and setter methods handling date-time in the java.sql.PreparedStatement class.}

This can further be clarified by checking the invocation of setTimestampInternal() method of the MySQL JDBC driver implementation.

See the following two calls to the setTimestampInternal() method from within the two overloaded versions of the setTimestamp() method.

/**
 * Set a parameter to a java.sql.Timestamp value. The driver converts this
 * to a SQL TIMESTAMP value when it sends it to the database.
 *
 * @param parameterIndex the first parameter is 1...
 * @param x the parameter value
 *
 * @throws SQLException if a database access error occurs
 */
public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
    setTimestampInternal(parameterIndex, x, this.connection.getDefaultTimeZone());
}

/**
 * Set a parameter to a java.sql.Timestamp value. The driver converts this
 * to a SQL TIMESTAMP value when it sends it to the database.
 *
 * @param parameterIndex the first parameter is 1, the second is 2, ...
 * @param x the parameter value
 * @param cal the calendar specifying the timezone to use
 *
 * @throws SQLException if a database-access error occurs.
 */
public void setTimestamp(int parameterIndex, java.sql.Timestamp x,Calendar cal) throws SQLException {
    setTimestampInternal(parameterIndex, x, cal.getTimeZone());
}

So, when no Calendar instance is specified with the PreparedStatement#setTimestamp() method, the default timezone will be used (this.connection.getDefaultTimeZone()) or the specified timezone with the supplied Calendar instance will be used (cal.getTimeZone()).

Therefore, just pass an instance of java.util.Calendar or its subclass java.util.GregorianCalendar to PreparedStatement#setTimestamp() with an appropriate timezone of your interest (UTC which we are interested in).

Timestamp timestamp = new Timestamp(new java.util.Date().getTime());
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

preparedStatement.setTimestamp(1, timestamp, calendar);

The specified timezone will be used to generate a string representation of the Timestamp being supplied before passing it to the underlying database.

If you happened to deal with an ORM framework like Hibernate, EclipseLink along with creating a connection pool in an application server / Servlet container using an appropriate datasource, then refer to this answer which this answer is based on and is directly copied from.

Inserting date / time in a specific zone into MySQL database

2 Answers2

Linked