The exported date is 43 seconds longer

[yangrongzhou]

Test code:

var sheetData = {
    '!ref': 'A1:A2',
    'A1': { v: '2021-08-12 00:00:00', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
    'A2': { v: '2021-08-12 00:27:02', t: 'd', z: 'yyyy-mm-dd hh:mm:ss' },
};

var sheetName = 'Sheet1';
var filename = "test.xlsx";

var workbook = {
    SheetNames: [sheetName],
    Sheets: {}
};

workbook.Sheets[sheetName] = sheetData;

XLSX.writeFile(workbook, filename, { compression: true });

But the Excel view result is:

2021-08-12 00:00:43
2021-08-12 00:27:45

My PC timezone is UTC+8.
Maybe this code can help you find the bug:

var basedate = new Date(1899, 11, 30, 0, 0, 0);
console.log(basedate.getTimezoneOffset()); // -485
console.log(basedate.toISOString()); // "1899-12-29T15:54:17.000Z"

For the date 1899-12-30 00:00:00, method getTimezoneOffset returns 43 seconds less.

[woshiguabi]

This article(language:Chinese) explained the issue. https://zhuanlan.zhihu.com/p/89914219

The time difference between a time zone and a zero time zone is different in various historical periods.

@SheetJSDev
Sheetjs use getTimezoneOffset to fix this issue, but getTimezoneOffset only returns minutes, not include seconds.

sheetjs/bits/20_jsutils.js

Lines 34 to 51 in 333deae

	var basedate = new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
	function datenum(v/*:Date*/, date1904/*:?boolean*/)/*:number*/ {
	var epoch = v.getTime();
	if(date1904) epoch -= 1462*24*60*60*1000;
	var dnthresh = basedate.getTime() + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
	return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
	}
	var refdate = new Date();
	var dnthresh = basedate.getTime() + (refdate.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
	var refoffset = refdate.getTimezoneOffset();
	function numdate(v/*:number*/)/*:Date*/ {
	var out = new Date();
	out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
	if (out.getTimezoneOffset() !== refoffset) {
	out.setTime(out.getTime() + (out.getTimezoneOffset() - refoffset) * 60000);
	}
	return out;
	}

So every Date before 1901-01-01 00:00:00 in timezone Shanghai will be 43 seconds ahead.

Maybe we can use Date.UTC to fix this issue.

Related

#1470 (comment)

---

Update

I use an extra offset to fix this bug.
Notice: Only xlsx ext needs to fix.

const baseDate = new Date(1899, 11, 30, 0, 0, 0);
const baseDateUtc = new Date(Date.UTC(1899, 11, 30, 0, 0, 0));
const timezoneOffsetFix =
  baseDateUtc.valueOf() +
  baseDate.getTimezoneOffset() * 60000 -
  baseDate.valueOf();

const date = moment('2021-08-12 00:00:00').toDate();
const needFix = new Date(date.valueOf() - timezoneOffsetFix).getTimezoneOffset() !== baseDate.getTimezoneOffset();
const fixedDate = needFix ? new Date(date.valueOf() - timezoneOffsetFix) : date;
console.log(moment(fixedDate).format('YYYY-MM-DD HH:mm:ss')); // 2021-08-11 23:59:17

[SheetJSDev]

The date bug is well known, #1918 (comment) was a comment from last year. Closing as dupe of #1565