PHPExcel_Shared_Date::PHPToExcel ignores timezones

[pwaring]

I've been having problems recently with converting Unix timestamps to Excel dates, whereby the date stored in Excel is one hour before what it should be (e.g. 01/06/2012 00:00 becomes 31/05/2012 23:00). On examining the code, I noticed that the PHPToExcel function in the PHPExcel_Shared_Date class sets the timezone to UTC before converting the date. My server is in BST (GMT+1) and the timezone is set to Europe/London everywhere else.

Is there a good reason why the function overrides the default timezone? I'm tempted to comment out the lines which do this because it's causing a major problem with the reports we generate, but I don't know whether that will affect other parts of PHPExcel.

[MarkBaker]

PHPExcel expects a UST date/time for date time values to provide consistency.

[pwaring]

That makes sense if the rest of the codebase assumes UTC. Could the phpdoc be updated to make this clear though please? There's no reference to expecting a UTC date/time and I spent ages trying to work out why the dates were not what I expected - it was only when I went in and read the code of the function that I realised it was overriding the timezone.

[MarkBaker]

As a potential option to handle this, I might modify the conversion handling methods to include an optional allowForTimeZone option when the input or output is a unix timestamp. Default will remain an assumption of UST unless allowForTimeZone is TRUE. When working with DateTime objects, timezone adjustment is automatic, so it isn't needed in that case.

[datvance]

The PHPToExcel method now accepts two new parameters: "adujstToTimezone" and "timezone", but they are ignored. Is this a bug, an oversight or by design?

[asheshambasta]

PHPExcel is an ok package written for excel exports and it shines only because not many others exist. I must say I'm impressed by how much has been attempted to be done, but its small annoying bugs and quirks like this that make me lose confidence in the whole thing.

I can confirm what @datvance has said, its just silly.

[1963669]

I just ran into this. Previously I was entering dates as text, then I discovered the PHPExcel_Shared_Date::PHPToExcel() function which was needed for anyone sorting by dates after the spreadsheet was generated.

I decided the easiest fix was to comment out as pwaring suggested the UTC default timezone swap: //$saveTimeZone = date_default_timezone_get(); //date_default_timezone_set('UTC'); [snip] //date_default_timezone_set($saveTimeZone);

For me this was in Shared/Date.php starting at line 153, but I know I'm on an older version of PHPExcel.

[dolphinotaku]

why did the UTC should be as a default and consistency. I think the timezone always dependence on the localization, moreover it doesn't make sense use +, in my timezone UTC+8 should be - here. https://github.com/PHPOffice/PHPExcel/blob/1.8/Classes/PHPExcel/Shared/Date.php#L145

[MarkBaker]

UTC is the default because MS Excel uses UTC Internally