Bug 53092 - Add a Cell.getDateCellValue(TimeZone timezone) method to allow user to specify required TimeZone for the Date
Summary: Add a Cell.getDateCellValue(TimeZone timezone) method to allow user to specif...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.8-FINAL
Hardware: All All
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-04-17 11:14 UTC by Gareth Western
Modified: 2012-04-27 15:50 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Gareth Western 2012-04-17 11:14:55 UTC
We encountered a problem in our application when reading a spreadsheet containing a series of dates without any timezone information. These dates fell on the first day of European Daylight Savings Time (March 25th, 2012):

For example:
25/03/2012 00:00
25/03/2012 01:00
25/03/2012 02:00
25/03/2012 03:00
25/03/2012 04:00

The application reading these dates runs on a server configured for the Europe/Oslo timezone (which uses DST) but the application needs these dates using the GMT calendar (which does not use DST).

So we first read the dates using Cell.getDateCellValue(), which returns a Date using Oslo time (e.g. "25/03/2012 00:00 CET"). We then create a Calendar using the GMT timezone and set the time values accordingly (e.g. resulting in "25/03/2012 00:00 GMT"). But the problem occurs when we read the "special" hour of 02:00, because when this is created as a Date using CET it is automatically adjusted to CEST (i.e. "02:00" becomes "02:00 CET" which becomes "03:00 CEST" which is actually "01:00 GMT"). We have worked around the problem by using the getNumericCellValue() and manually parsing the Date ourselves, however it would be much more convenient if we were able to specify the required TimeZone in the first place, which would be used by the POI DateUtil.getJavaDate() method to create a Calendar using the correct timezone in the first place.

We cannot simply change the default JVM TimeZone as the JVM is running several instances of our application for several different timezones.

Should I create a patch, or is the aforementioned Use Case enough of a description?

Kind Regards,
Gareth
Comment 1 Nick Burch 2012-04-17 11:54:44 UTC
I'm not sure about changing the Cell class, but I don't see why we can't allow control of the timezone on DateUtil. People with special Date needs can then use that for full control, and DateUtil.isCellDateFormatted(cell) to check if a numeric cell is a date one or not
Comment 2 Gareth Western 2012-04-17 12:50:56 UTC
Yeah, that would be fine too. Thanks!
Comment 3 Nick Burch 2012-04-27 15:50:55 UTC
As of r1331477, DateUtil.getJavaDate now optionally takes a timezone. If you know which timezone a file comes from (not stored in the file alas), you can use this to turn date numbers into Java Dates in that timezone