Time in our current universe can be modelled using 28 granularities, which is kind of cool because 28 is the idealized period of the cycle of the moon, and breaks down into 7 * 4.

But main reason is because it can model every degree of granularity of time. Consider it a "timespan" model, and below are some old models I used, with the SQL being the final model.

The time table can be used for a duration, or a point.

A timespan can be represented as stuff like this:

{ GIGAYEAR: 4, MEGAYEAR: 6 } // 4.6 billion years

Here are the tables now.

CREATE TABLE "time" (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "l1" INTEGER,
  "l2" INTEGER,
  "l3" INTEGER,
  "l4" INTEGER,
  "l5" INTEGER,
  "l6" INTEGER,
  "l7" INTEGER,
  "l8" INTEGER,
  "l9" INTEGER,
  "l10" INTEGER,
  "l11" INTEGER,
  "l12" INTEGER,
  "l13" INTEGER,
  "l14" INTEGER,
  "granularity" SMALLINT,
  "is_micro" BOOLEAN,
  "variance__id" BIGINT
);

export enum TimeScale {
  E45_SECOND = 1, // 1e-45 s
  // Planck scale is ~5.9e-44
  E42_SECOND = 2, // 1e-42 s
  E39_SECOND = 3, // 1e-39 s
  E36_SECOND = 4, // 1e-36 s
  E33_SECOND = 5, // 1e-33 s
  E30_SECOND = 6, // 1e-30 s
  E27_SECOND = 7, // 1e-27 s
  E24_SECOND = 8, // 1e-24 s (yoctosecond)
  E21_SECOND = 9, // 1e-21 s (zeptosecond)
  E18_SECOND = 10, // 1e-18 s (attosecond)
  E15_SECOND = 11, // 1e-15 s (femtosecond)
  E12_SECOND = 12, // 1e-12 s (picosecond)
  E9_SECOND = 13, // 1e-9 s  (nanosecond)
  E6_SECOND = 14, // 1e-6 s  (microsecond)
  E3_SECOND = 15, // 1e-3 s  (millisecond)
  SECOND = 16, // 1 s
  MINUTE = 17, // 60 s
  HOUR = 18, // 3600 s
  DAY = 19, // 86400 s
  MONTH = 20, // ~2.63e6 s
  YEAR = 21, // ~3.154e7 s
  DECADE = 22, // ~3.154e8 s
  CENTURY = 23, // ~3.154e9 s
  MILLENNIUM = 24, // ~3.154e10 s
  KILOYEAR = 25, // 1e3 years
  MEGAYEAR = 26, // 1e6 years
  GIGAYEAR = 27, // 1e9 years
  TERAYEAR = 28, // 1e12 years
}

• is_micro: if true, then these 14 points count for the lower end of the spectrum (below 1e-3 seconds)
• granularity: Is where everything below is not counted, so if is_micro: false, and granularity is 2, then that is treating seconds and below as 0.
• variability: another time, counting as plus/minus duration of how much this time point might be off by. Useful for "circa 1500s" type stuff (variability +- 100 years)