SQL dates, seconds since epoch and GMT/BST differences

Hi all,

I've got some code which takes a date or a date/time combination in a String, parses it and creates SQL dates and times. However it seems to produce incorrect results based on GMT and BST differences;

package com.ms.mcparlan.dates;

import java.sql.Date;
import java.sql.Timestamp;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;

/**
 * Tests the formatting of SQL dates
 * <p>
 * See http://www.direct.gov.uk/en/Governmentcitizensandrights/LivingintheUK/DG_073741 for BST dates
 * </p>
 * 
 * @author John McParland
 */
public class SQLDates {

/**
     * Create an SQLDates instance to perform tests
     */
    public SQLDates() {
        Locale.setDefault(Locale.UK);
    }

/**
     * Format for dates
     */
    private static final String DATE_FORMAT = "yyyyMMdd";

/**
     * Format for date/times
     */
    private static final String DATE_TIME_FORMAT = "yyyyMMdd-HH:mm:ss";

/**
     * Create the string indicating a failure
     * 
     * @param testName
     *            The name of the test which failed
     * @param expected
     *            The expected seconds since the epoch
     * @param actual
     *            The actual seconds since the epoch
     * @return A string with all the information to diagnose the problem
     */
    private String createFailString(String testName, long expected, long actual) {
        return testName + ": FAIL (expected == " + expected + "[" + format(expected) + "]" + ", acutal == " + actual
                + "[" + format(actual) + "]" + ")";
    }

/**
     * Format the time given
     * @param millis The milliseconds since the epoch
     * @return The formatted date/time in yyyyMMdd-hh:mm:ss
     */
    private String format(long millis) {
        java.sql.Date date = new java.sql.Date(millis);
        return new SimpleDateFormat(DATE_TIME_FORMAT).format(date);
    }

/**
     * Test BST dates are parsed correctly
     */
    public void testBSTDates() {
        final String dateStr = "20100601"; // 1st of June 2010(BST)
        final Calendar cal = Calendar.getInstance();
        cal.set(2010, 5, 1, 0, 0, 0); // June is 5 for Calendar
        cal.set(Calendar.MILLISECOND, 0);
        final long sinceEpoch = cal.getTimeInMillis();
        Date date = createDate(dateStr);
        if (date.getTime() == sinceEpoch) {
            System.out.println("testBSTDates: PASS");
        } else {
            System.out.println(createFailString("testGMTTimes", sinceEpoch, date.getTime()));
        }
    }

/**
     * Test the GMT dates are parsed correctly
     */
    public void testGMTDates() {
        final String dateStr = "20100101"; // 1st of Feb 2010 (GMT)
        final Calendar cal = Calendar.getInstance();
        cal.set(2010, 1, 1, 0, 0, 0); // Feb is 1 for Calendar
        cal.set(Calendar.MILLISECOND, 0);
        final long sinceEpoch = cal.getTimeInMillis();
        Date date = createDate(dateStr);
        if (date.getTime() == sinceEpoch) {
            System.out.println("testGMTDates: PASS");
        } else {
            System.out.println(createFailString("testGMTDates", sinceEpoch, date.getTime()));
        }
    }

/**
     * Test the BST times are parsed correctly
     */
    public void testBSTTimes() {
        final String timeStr = "20100601-15:39:23"; // 1st of June 2010 15:39:23 (BST)
        final Calendar cal = Calendar.getInstance();
        cal.set(2010, 5, 1, 15, 39, 23); // June is 5 for Calendar
        cal.set(Calendar.MILLISECOND, 0);
        final long sinceEpoch = cal.getTimeInMillis();
        Timestamp time = createTimestamp(timeStr);
        if (time.getTime() == sinceEpoch) {
            System.out.println("testBSTTimes: PASS");
        } else {
            System.out.println(createFailString("testBSTDates", sinceEpoch, time.getTime()));
        }
    }

/**
     * Test the GMT times are parsed correctly
     */
    public void testGMTTimes() {
        final String timeStr = "20100201-15:39:23"; // 1st of Feb 2010 15:39:23 (GMT)
        final Calendar cal = Calendar.getInstance();
        cal.set(2010, 1, 1, 15, 39, 23); // Feb is 1 for Calendar
        cal.set(Calendar.MILLISECOND, 0);
        final long sinceEpoch = cal.getTimeInMillis();
        Timestamp time = createTimestamp(timeStr);
        if (time.getTime() == sinceEpoch) {
            System.out.println("testGMTTimes: PASS");
        } else {
            System.out.println(createFailString("testGMTTimes", sinceEpoch, time.getTime()));
        }
    }

/**
     * Create the date
     * 
     * @param dateStr
     *            The date string, format must be yyyyMMdd
     * @return An SQL date
     */
    public Date createDate(String dateStr) {
        if (null != dateStr) {
            SimpleDateFormat dtFmt = new SimpleDateFormat(DATE_FORMAT);
            ParsePosition parsePos = new ParsePosition(0); // mimic'ing production
            parsePos.setIndex(0);
            Date dt = new Date(dtFmt.parse(dateStr, parsePos).getTime());
            return dt;
        } else {
            throw new IllegalArgumentException("dateStr must be non-null");
        }
    }

/**
     * Create the timestamp
     * 
     * @param timeStr
     *            The date/time string, format must be yyyyMMdd-hh:mm:ss
     * @return An SQL Timestamp
     */
    public Timestamp createTimestamp(String timeStr) {
        if (null != timeStr) {
            SimpleDateFormat dtFmt = new SimpleDateFormat(DATE_TIME_FORMAT);
            ParsePosition parsePos = new ParsePosition(0); // mimic'ing production
            parsePos.setIndex(0);
            Timestamp time = new Timestamp(dtFmt.parse(timeStr, parsePos).getTime());
            return time;
        } else {
            throw new IllegalArgumentException("timeStr must be non-null");
        }
    }

/**
     * Entry point to program
     * 
     * @param args
     *            Program arguments, not required, will be ignored if provided
     */
    public static void main(String[] args) {
        SQLDates dates = new SQLDates();

// Do the testing

// Dates only
        dates.testBSTDates();
        dates.testGMTDates();

// Date and time (to second precision)
        dates.testBSTTimes();
        dates.testGMTTimes();
    }

}

Running on my machine (UK timezone, currently in BST), the testGMTDates fails!

I don't understand why this happens. I'm particularly perplexed that the testGMTDates puts the same date as the testGMTTimes in an earlier month! Any pointers?

Many thanks,

John

And within seconds of posting, I feel like a numpty as I can now see the issue (I'll say it's because explaining it to someone else helps clarify the issue);

From testGMTDates;

From testGMTTimes;

The month (6th character of the string) is wrong in dateStr / testGMTDates.

As a side note: do not use online epoch converters for Java code, they always gave different times for me depending on whether I was running on a UK or US machine due to GMT / BST differences.

Use Calendar#getInstance(), Calendar#set() and Calendar#getTimeInMillis() to get parsing against the real date / time.

Thank you for sharing, John.