Read excel cell (timestep) unit test failing (using Date)

Dear all,

I'm reading data from an excel file and saving it into a class using code block 1.  In the excel file the cell is of type 'TIME' and an example of a value in a cell is "12:30:00 AM".  The input file has not date information.

I don't think I'm reading the cell value correctly.  Unit test 1 is failing.  Expected 12 but was 10.

I started writing unit test 2 (code block 3), then noticed the output is totally wrong,  I was planning on saving the date/time as a long, then convert to date and use calendar to access the HH:MM.  

The aim is finding the matching time steps to retrieve values, the long timestep from code block 1 and the map containing long timeMS from code block 4.

If you have a better solution please let me know.  Thanks.

Code block 1

long timestep = new SimpleDateFormat("HH:mm").parse(details[COL_TIME]).getTime();

Open in new window

Code block 2 - Unit Test 1

	public void testValidateTime() throws ParseException {
		String testString = "12:30:00 AM";
		long actual = new SimpleDateFormat("HH:mm").parse(testString).getTime();
		Date d = new Date(actual);
		Calendar cal = Calendar.getInstance();
		assertEquals(12, Calendar.HOUR); // expected 12 but was 10
		assertEquals(30, Calendar.MINUTE);

Open in new window

Code block 3 - Unit Test 2

Unit Test 2 is returning Time: Thu Mar 19 18:35:34 EST 2015 which is totally wrong.  

scenarioList  contains a list of Scenario objects.

	public void testFindScenarioTime() { 

		for (int i = 0; i < scenarioList.size(); i++) {
			Scenarios s = scenarioList.get(i);
			long timestep = s.getTimestep();
			Date d = new Date();
			Calendar startCal = Calendar.getInstance();
			System.out.println("Time: " + startCal.getTime());

Open in new window

Code block 4

Map<Long, Double> map = new HashMap<>();

		long timeMS = start.getTime();
		while (timeMS < end.getTime()) {
			// Store the data in the map
			map.put(timeMS, 0.0); // 0.0 default value for testing
			timeMS += TimeUnit.MILLISECONDS.convert(30L, TimeUnit.MINUTES);
			System.out.println(new Date(timeMS));

Open in new window

Who is Participating?
Unit test 1 needs to be written as follows:
	public void testValidateTime() throws ParseException {
		String testString = "12:30:00 AM";
		long actual = new SimpleDateFormat("hh:mm:ss a").parse(testString).getTime();
		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(actual); //no need to create a data object since you alread have the time in milliseconds
		assertEquals(12, cal.get(Calendar.HOUR_OF_DAY));  //each half of the day goes from 0 to the end of the 11th hour
		assertEquals(30,  cal.get(Calendar.MINUTE));

Open in new window

1)I have not created a new java.util.Date object since we already have the time in milliseconds

2)I have changed the date format to be "hh:mm:ss a" since this represents 12 hour clock with a AM/PM marker at the end.

3)In your asserts you are simply asserting the actual hours against the Calendar constant for java.util.Calendar.HOUR which is always going to be 10. So I have corrected the asserts to called the get() method on the Calendar instance.

4)When asserting for hours you have to remember that each half of the day goes from hour zero until the end of the 11th hour. Since your input time from Excel is going to be in 12-HR format, we can use the HOUR_OF_DAY constant.

This should enable you to parse your date into a long correctly and pull it back out from the Map as a key.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.