PHPToExcel::timestampToExcel uses GMT timezone

[Bhoft]

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

I am using the Date::PHPToExcel($ts); with a unix timestamp and was wondering why the timezone which i set wasn't used.

What is the current behavior?

My current timezone set in my application is 'Europe\Berlin' i add some cells with datetimes to the Excel file and the Timezone of those fields isn't correct when i used unix timestamps.

What are the steps to reproduce?

<?php
require_once __DIR__ . '/vendor/autoload.php';

ob_start();
// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...
date_default_timezone_set('Europe/Berlin');

\PhpOffice\PhpSpreadsheet\Shared\Date::setDefaultTimezone(date_default_timezone_get());
\PhpOffice\PhpSpreadsheet\Shared\TimeZone::setTimeZone(date_default_timezone_get());

$dateString = '2020-04-01 00:00:00';
$unixTimestamp = strtotime($dateString);
var_dump($unixTimestamp);

$dateObject = DateTime::createFromFormat("U", $unixTimestamp)->setTimeZone(new DateTimeZone(date_default_timezone_get()));
var_dump($dateObject);

#draw the specified date time to excel
$worksheet = $spreadsheet->setActiveSheetIndex(0);


#displays 1/4/20 0:00
$worksheet
    ->getCellByColumnAndRow(1, 1)
    ->setValue(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dateString))
    ->getStyle()
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

#displays: 1/4/20 0:00
$worksheet
    ->getCellByColumnAndRow(1, 2)
    ->setValue(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dateObject))
    ->getStyle()
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

#displays: 31/3/20 22:00
$worksheet
    ->getCellByColumnAndRow(1, 3)
    ->setValue(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($unixTimestamp))
    ->getStyle()
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);


var_dump(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dateString));
var_dump(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($dateObject));
var_dump(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($unixTimestamp));

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);

if (isset($_GET['debug']) && ! $_GET['debug']) {
    ob_end_clean();
    $filename = 'test.xlsx';
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-type: ' . 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename .'"');
    header('Cache-Control: max-age=0');
    $writer->save('php://output');
} else {
    $out = ob_get_contents();
    ob_end_clean();
    echo $out;
}
      

Output

$unixTimestamp
test.php:19:int 1585692000

$dateObject
test.php:22:
object(DateTime)[93]
  public 'date' => string '2020-04-01 00:00:00.000000' (length=26)
  public 'timezone_type' => int 3
  public 'timezone' => string 'Europe/Berlin' (length=13)

Date::PHPToExcel($dateString)
test.php:53:float 43922

Date::PHPToExcel($dateObject)
test.php:54:float 43922

Date::PHPToExcel($unixTimestamp)
test.php:55:float 43921.916666667

Problem comes from this function in Shared/Date.php which creates the dates when a timestamp is given to PHPToExcel.

    public static function timestampToExcel($dateValue)
    {
        if (!is_numeric($dateValue)) {
            return false;
        }

        return self::dateTimeToExcel(new \DateTime('@' . $dateValue));
    }

The DateTime constructor ignores any Timezones for UNIX timestamp.

Wouldn't it be more correct if the function creates the Datetime in the timezone which was set by Date::setDefaultTimezone and is returned by Date::getDefaultTimezone?

 /**
 * Return the Default timezone being used for dates.
 *
 * @return DateTimeZone The timezone being used as default for Excel timestamp to PHP DateTime object
 */
public static function getDefaultTimezone()
{
    if (self::$defaultTimeZone === null) {
        self::$defaultTimeZone = new DateTimeZone('UTC');
    }

    return self::$defaultTimeZone;
}

e.g. modify the timestampToExcel to use this default Timezone or PHP's default Timezone?

        public static function timestampToExcel($dateValue)
        {
            if (!is_numeric($dateValue)) {
                return false;
            }

            return self::dateTimeToExcel(\DateTime::createFromFormat("U", $dateValue)->setTimeZone(Date::getDefaultTimezone()));
            // or PHP default timezone?
            return self::dateTimeToExcel(\DateTime::createFromFormat("U", $dateValue)->setTimeZone(new \DateTimeZone(date_default_timezone_get())));
        }

This issue was also discussed here:
#611

Or at least add some text to the documentation that the function does work differently for Unix Timestamps as nothing is mentioned there:
https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Shared-Date.html#method_PHPToExcel

[oleibman]

The use of Unix timestamps rather than DateTime objects is problematic on many levels. I even suggested deprecating such usage (see PR #1870), although we decided that they were probably still too much in use to do so at this time. Nevertheless, your suggestion to update the doc block might be worthwhile; I will look into it.

[oleibman]

Documentation updated by 2350, as requested. Closing.