From 9658ea05680a3c9e84d7a09de66776be9cb2fc26 Mon Sep 17 00:00:00 2001 From: Ardis Lu Date: Wed, 24 Sep 2025 23:00:11 -0700 Subject: [PATCH] Parse Unix timestamps These three ways of representing dates as a number each have distinct magnitudes. I picked arbitrary cutoff dates to switch the logic under the assumption that values commonly seen in CSVs would not cross those cutoff dates. --- src/normalization.js | 22 +++++++++++++++++----- test/normalization.test.js | 29 ++++++++++++++++++----------- 2 files changed, 35 insertions(+), 16 deletions(-) diff --git a/src/normalization.js b/src/normalization.js index b140539..ee04dc0 100644 --- a/src/normalization.js +++ b/src/normalization.js @@ -276,14 +276,26 @@ export class CSVNormalizer extends TransformStream { // Excel converted date to a number // This check MUST come before the new Date() check because numbers can be parsed as dates. + // Assumptions about what the number represents, with date bounds: + // num <= 200000 (2447-07-30): native Excel date + // 200000 (1970-01-03) < num <= 10000000000 (2286-11-20): Unix timestamp (seconds) + // 10000000000 (1970-04-26) < num: Unix timestamp (milliseconds) const num = Number(original); if (!Number.isNaN(num)) { - const day = Math.trunc(num); // Days elapsed since January 1, 1900, including non-existent February 29, 1900 - const time = (num % 1) * 86400000; // Time of day as decimal, so 0 = 12 AM and 0.999... = 11:59:59.999... PM - if (num < 61) { - return new Date(Date.UTC(0, 0, day, 0, 0, 0, time)); + if (num <= 200000) { // Assume it's a native Excel date casted to number + const day = Math.trunc(num); // Days elapsed since January 1, 1900, including non-existent February 29, 1900 + const time = (num % 1) * 86400000; // Time of day as decimal, so 0 = 12 AM and 0.999... = 11:59:59.999... PM + if (num < 61) { + return new Date(Date.UTC(0, 0, day, 0, 0, 0, time)); + } + return new Date(Date.UTC(0, 0, day - 1, 0, 0, 0, time)); // Offset for non-existent February 29, 1900 + } + else if (200000 < num && num <= 10000000000) { // Assume it's a Unix timestamp (seconds) + return new Date(num * 1000); + } + else { // Assume it's a Unix timestamp (milliseconds) + return new Date(num); } - return new Date(Date.UTC(0, 0, day - 1, 0, 0, 0, time)); // Offset for non-existent February 29, 1900 } // Can parse normally without any fixes diff --git a/test/normalization.test.js b/test/normalization.test.js index bef63dd..d4a1821 100644 --- a/test/normalization.test.js +++ b/test/normalization.test.js @@ -144,17 +144,24 @@ suite('CSVNormalizer.fixExcelBigInt', { concurrency: true }, () => { suite('CSVNormalizer.fixExcelDate', { concurrency: true }, () => { const vectors = [ { name: 'parses date', input: '12/31/24', output: '12/31/24' }, - { name: 'fixes number mangling (1)', input: '1', output: '1900-01-01T00:00:00.000Z' }, - { name: 'fixes number mangling (2)', input: '2', output: '1900-01-02T00:00:00.000Z' }, - { name: 'fixes number mangling (59)', input: '59', output: '1900-02-28T00:00:00.000Z' }, - { name: 'fixes number mangling (60)', input: '60', output: '1900-02-29T00:00:00.000Z' }, - { name: 'fixes number mangling (61)', input: '61', output: '1900-03-01T00:00:00.000Z' }, - { name: 'fixes number mangling (25568)', input: '25568', output: '1969-12-31T00:00:00.000Z' }, - { name: 'fixes number mangling (25569)', input: '25569', output: '1970-01-01T00:00:00.000Z' }, - { name: 'fixes number mangling (25570)', input: '25570', output: '1970-01-02T00:00:00.000Z' }, - { name: 'fixes number mangling (43915.31372)', input: '43915.31372', output: '2020-03-25T07:31:45.407Z' }, - { name: 'fixes number mangling (44067.63)', input: '44067.63', output: '2020-08-24T15:07:11.999Z' }, - { name: 'fixes number mangling (44309.63502)', input: '44309.63502', output: '2021-04-23T15:14:25.728Z' }, + { name: 'fixes number mangling (1) (Excel native date)', input: '1', output: '1900-01-01T00:00:00.000Z' }, + { name: 'fixes number mangling (2) (Excel native date)', input: '2', output: '1900-01-02T00:00:00.000Z' }, + { name: 'fixes number mangling (59) (Excel native date)', input: '59', output: '1900-02-28T00:00:00.000Z' }, + { name: 'fixes number mangling (60) (Excel native date)', input: '60', output: '1900-02-29T00:00:00.000Z' }, + { name: 'fixes number mangling (61) (Excel native date)', input: '61', output: '1900-03-01T00:00:00.000Z' }, + { name: 'fixes number mangling (25568) (Excel native date)', input: '25568', output: '1969-12-31T00:00:00.000Z' }, + { name: 'fixes number mangling (25569) (Excel native date)', input: '25569', output: '1970-01-01T00:00:00.000Z' }, + { name: 'fixes number mangling (25570) (Excel native date)', input: '25570', output: '1970-01-02T00:00:00.000Z' }, + { name: 'fixes number mangling (43915.31372) (Excel native date)', input: '43915.31372', output: '2020-03-25T07:31:45.407Z' }, + { name: 'fixes number mangling (44067.63) (Excel native date)', input: '44067.63', output: '2020-08-24T15:07:11.999Z' }, + { name: 'fixes number mangling (44309.63502) (Excel native date)', input: '44309.63502', output: '2021-04-23T15:14:25.728Z' }, + { name: 'fixes number mangling (200001) (Unix seconds)', input: '200001', output: '1970-01-03T07:33:21.000Z' }, + { name: 'fixes number mangling (1585121505) (Unix seconds)', input: '1585121505', output: '2020-03-25T07:31:45.000Z' }, + { name: 'fixes number mangling (10000000000) (Unix seconds)', input: '10000000000', output: '2286-11-20T17:46:40.000Z' }, + { name: 'fixes number mangling (10000000001) (Unix milliseconds)', input: '10000000001', output: '1970-04-26T17:46:40.001Z' }, + { name: 'fixes number mangling (1585121505407) (Unix milliseconds)', input: '1585121505407', output: '2020-03-25T07:31:45.407Z' }, + { name: 'fixes number mangling (1.58512E+12) (Unix milliseconds)', input: '1.58512E+12', output: '2020-03-25T07:06:40.000Z' }, + { name: 'fixes number mangling (32535216000000) (Unix milliseconds)', input: '32535216000000', output: '3001-01-01T00:00:00.000Z' }, { name: 'passes through non-date values', input: 'abc', output: 'abc', raw: true, consoleCounts: { warn: 1 } }, { name: 'passes through blank value', input: '', output: '', raw: true }, ]; -- 2.47.3