Java - TimeZone conversion issue with SQL timestamp

Question

I am using MySQL database on the Server. My Web application Server is in US/Chicago but I want my DateTime values to be saved in Africa/Lagos time.

I got part of this code here from Stack Overflow but when I update a record it still shows Chicago time in the database record. Please what am I doing wrong here?

public static boolean saveImageFileName(int id, String fileName) throws SQLException, IOException, IllegalArgumentException, ClassNotFoundException
{
    try(Connection conn = Config.getDatabaseConnection())
    {
        //Create a timezone object based on Africa/Lagos time zone
        SimpleTimeZone timeZone = new SimpleTimeZone(1, "Africa/Lagos");
        //Create a calendar object using the timezone object
        GregorianCalendar calendar = new GregorianCalendar(timeZone);
        //Create a timestamp object using the calendar's date and time
        Timestamp timestamp = new Timestamp(calendar.getTime().getTime());
        //Create an SQL query String
        String sql = "UPDATE `requests` SET `filename` = ?, `uploaded_date` = ? WHERE `id` = ?";
        //Create a prepared statement object from database connection
        PreparedStatement pst = conn.prepareStatement(sql);
        //Set prepared statement parameters
        pst.setString(1, fileName);
        pst.setTimestamp(2, timestamp, calendar); //Set TimeStamp and Calendar
        pst.setInt(3, id);
        //Update the record
        int update = pst.executeUpdate();
        //return update result
        return update == 1;
    }
}

Basil Bourque · Accepted Answer · 2017-06-04 19:54:33Z

tl;dr

pst.setObject( 2 , Instant.now() ) ;  // Current moment in UTC.

…and…

Instant instant = myResultSet.getObject( 2 , Instant.class ) ;  // Stored moment in UTC.
ZonedDateTime zdt = instant.atZone( ZoneId.of( "Africa/Lagos" ) ) ;   // Adjust from UTC to time zone. Same moment, same point on timeline, but different wall-clock time.

Do not rely on `toString`

Apparently you are being confused by the output of the legacy date-time classes’ toString method. Among the many poor design decisions in those old classes was the anti-feature of applying the JVM’s current time zone while generating a String to textually represent the value of that date-time object. This creates the confusing illusion that the object has a time zone when it does not.

So you cannot use toString on those old classes as it does not faithfully represent the true value.

Using java.time

You are using troublesome old date-time classes that are now legacy, supplanted by the java.time classes.

ZoneId z = ZoneId.of( "Africa/Lagos" ) ) ;
ZonedDateTime zdt = ZonedDateTime.now( zdt ) ;

But the time zone is irrelevant here as a java.sql.Timestamp is always in UTC. The equivalent is a Instant in java.time. You can extract an Instant from the ZonedDateTime or get the current moment in UTC as a Instant.

Instant instant = zdt.toInstant() ;  // Adjusting from a time zone to UTC.

Or…

Instant instant = Instant.now() ;  // Current moment in UTC.

If your JDBC driver complies with JDBC 4.2 or later, you can work directly with java.time types and forsake the legacy java.sql types. Call PreparedStatement::setObject and ResultSet::getObject.

If your JDBC has not yet been updated for java.time, convert to a java.sql.Timestamp using new methods added to the old classes.

java.sql.Timestamp ts = java.sql.Timestamp.from( instant ) ;

Going the other way, you can adjust an Instant into a particular time zone when you want to see that same moment through the lens of a particular region’s wall-clock time.

Instant instant = myResultSet.getObject( … , Instant.class )  // Or call: ts.toInstant()
ZonedDateTime zdt = instant.atZone( z ) ;

As for generating String objects to represent textually the value of these java.time objects, you can rely on calling the toString methods. The java.time classes by default use the sensible practical formats defined in the ISO 8601 standard.

Darshan Mehta · Accepted Answer · 2017-06-04 13:22:16Z

0

You need to pass Calendar object with TimeZone in setTimeZone method, as mentioned in this javadoc, try this:

pst.setTimestamp(2, timestamp, Calendar.getInstance(TimeZone.getTimeZone("Africa/Lagos")));

answered Jun 4, 2017 at 13:22

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

1 Comment

Jevison7x Over a year ago

It's still showing Chicago Time 2017-06-04 08:31:35 instead of 2017-06-04 14:31:35

Collectives™ on Stack Overflow

Java - TimeZone conversion issue with SQL timestamp

2 Answers 2

tl;dr

Do not rely on `toString`

Using java.time

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

tl;dr

Do not rely on toString

Using java.time

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

Do not rely on `toString`