Skip to content
@northguild/gmt

Foreign Epoch Bridges

  • The core functions, which every industry uses.

Other systems do not count from 1970. NTP counts from 1900 and rolls over in 2036. Windows FILETIME counts 100-nanosecond intervals from 1601. .NET ticks count the same interval from year 1. Excel uses a day serial with a deliberate 1900 leap-year bug. PostgreSQL stores microseconds from 2000-01-01.

Every integration re-derives these constants, usually wrongly. The precision/ namespace has a pair of functions for each: toNtpTimestamp and fromNtpTimestamp, toFileTime and fromFileTime, toDotNetTicks and fromDotNetTicks, toExcelSerial and fromExcelSerial, and toPgMicroseconds and fromPgMicroseconds. For plain Unix and nanosecond conversion, see Nanosecond Precision and Converting Between Types.

import {
  toNtpTimestamp,
  fromNtpTimestamp,
  toFileTime,
  fromFileTime,
  toDotNetTicks,
  fromDotNetTicks,
  toExcelSerial,
  fromExcelSerial,
  toPgMicroseconds,
  fromPgMicroseconds,
} from "@northguild/gmt";


toFileTime("1970-01-01T00:00:00Z");
// 116444736000000000n — 100 ns intervals since 1601-01-01


toDotNetTicks("1970-01-01T00:00:00Z");
// 621355968000000000n — same unit, counted from 0001-01-01


toPgMicroseconds("2024-03-10T12:00:00Z");
// 763387200000000n — microseconds since 2000-01-01

NTP timestamps are era-ambiguous by design

Section titled “NTP timestamps are era-ambiguous by design”

The seconds field is unsigned 32-bit, so it wraps every 136.19 years or so, and the era is not part of the value. fromNtpTimestamp takes it as an explicit argument:

toNtpTimestamp("2036-02-07T06:28:15Z");
// 18446744069414584320n — the last second of era 0


toNtpTimestamp("2036-02-07T06:28:16Z");
// 0n — era 1 begins, and the seconds field wraps


fromNtpTimestamp(0n);
// "1900-01-01T00:00:00Z"


fromNtpTimestamp(0n, 1);
// "2036-02-07T06:28:16Z" — the same value, one era on

Excel’s serial 60 is the phantom 29 February 1900

Section titled “Excel’s serial 60 is the phantom 29 February 1900”

That date never existed, because 1900 was not a leap year. Lotus 1-2-3 got it wrong and Excel keeps the bug for compatibility, so the serial day [60, 61) is a hole rather than a date.

“Fixing” it would put the library one day out from Excel for every date before March 1900.

fromExcelSerial(59);
// "1900-02-28T00:00:00Z"


fromExcelSerial(60);
// "" — the phantom 1900-02-29


fromExcelSerial(61);
// "1900-03-01T00:00:00Z"


toExcelSerial("2024-03-10T12:00:00Z");
// 45361.5 — days, with the time of day as the fraction


toExcelSerial("2024-03-10T12:00:00Z", { system: "1904" });
// 43899.5 — the legacy Mac system, exactly 1462 days lower

What each bridge accepts

Section titled “What each bridge accepts”

Each bridge accepts only what its target format can actually hold. It returns the namespace’s sentinel otherwise. toFileTime on a pre-1601 instant is 0n, not a negative tick count, and toExcelSerial past 9999-12-31 is null, not an out-of-range serial.

PostgreSQL’s range is the one that surprises. Its timestamp domain starts at 4713 BC, which is 40× later than the earliest instant Temporal can represent. An in-range Temporal instant is therefore not automatically an in-range PostgreSQL one.

Where the target unit is coarser than a nanosecond, the conversion floors toward negative infinity, so a round trip is exact to that unit:

  • 100 ns for FILETIME and .NET ticks
  • 1 µs for PostgreSQL
  • 1 ms for Excel

NTP is the exception in the other direction. Its 2^-32 s unit is finer than a nanosecond, so fromNtpTimestamp rounds to the nearest nanosecond and the round trip is exact.

See also

Section titled “See also”