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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.