Timetastic.java

package net.headondesk;

import java.sql.*;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.TimeZone;

public class Timetastic {

    Connection conn;

    public static void main(String[] args) {

        // Fiddling with JVM knowledge of the timezone. Not much impact for this code.
        //TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

        if (args.length == 1 && args[0].equals("w")) {
            new Timetastic("w");
        } else if (args.length == 1 && args[0].equals("r")) {
            new Timetastic("r");
        } else {
            System.out.println(" Single command line argument expected. Must be 'r' or 'w'.");
        }
    }

    public Timetastic(String mode) {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            // Fiddling with JDBC knowledge of the timezone.
            // Interesting take here - https://forums.mysql.com/read.php?39,392770,393096#msg-393096, the serverTimezone is a bit useless?
            String url = "jdbc:mysql://localhost/time_for_time?useLegacyDatetimeCode=false";
            conn = DriverManager.getConnection(url, "user", "password");
            conn.setAutoCommit(true);

            if (mode.equals("w")) {
                writeTimestamps();
            }
            readTimestamps();

            conn.close();
        }  catch (Exception ex) {
            System.err.println(ex.getMessage());
        }
    }

    public void writeTimestamps(){

        System.out.println("Writing times to the database.\n");

        System.out.println("UTC input");
        insertTimeIntoDatabase(convertTimeStringToInstant("2018-04-13T11:12:00Z"), "UTC");

        System.out.println("Europe/Amsterdam input");
        insertTimeIntoDatabase(convertTimeStringToInstant("2018-04-13T13:12:00+02:00"), "Europe/Amsterdam");

        System.out.println("Asia/Calcutta input");
        insertTimeIntoDatabase(convertTimeStringToInstant("2018-04-13T16:42:00+05:30"), "Asia/Calcutta");
    }


    public Instant convertTimeStringToInstant(String timeString){
        DateTimeFormatter f = DateTimeFormatter.ISO_OFFSET_DATE_TIME;
        Instant instant = Instant.from(f.parse(timeString));
        System.out.println(timeString + ", " + instant.getEpochSecond());
        return instant;
    }


    public void insertTimeIntoDatabase(Instant instant, String tz){
        try {
            PreparedStatement p = conn.prepareStatement("insert into basic_time values(?,?,?)");
            p.setString(1, tz);
            p.setTimestamp(2, Timestamp.from(instant), Calendar.getInstance(TimeZone.getTimeZone(tz)));
            p.setTimestamp(3, Timestamp.from(instant));
            p.executeUpdate();
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    public void readTimestamps() {
        System.out.println("\nReading stored times from database.\n");


        String query = "select tz, mydatetime, mytimestamp from basic_time";
        Statement stmt = null;

        System.out.println("Description, Datetime column (epoch seconds), Datetime column (as ISO string), Timestamp column (as epoch seconds), Timestamp column (as ISO string)");
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String tz = rs.getString("tz");
                Instant fromDatetime = rs.getTimestamp("mydatetime", Calendar.getInstance(TimeZone.getTimeZone(tz))).toInstant();
                Instant fromTimestamp = rs.getTimestamp("mytimestamp").toInstant();

                System.out.println(tz + ", "
                        + fromDatetime.getEpochSecond() + ", " + fromDatetime.atZone(ZoneId.of(tz)).toString() + ", "
                        + fromTimestamp.getEpochSecond() + ", " + fromTimestamp.atZone(ZoneId.of(tz)).toString());

            }
        } catch (SQLException ex ) {
            System.err.println(ex);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception ex){
                System.err.println(ex.getMessage());
            }
        }
    }
}