defmodule ExSQL.DateTime do
@moduledoc """
SQLite-compatible date/time scalar functions.
All computation follows SQLite's `src/date.c`:
- Internally everything runs as a fractional Julian Day Number times
86,400,000 (milliseconds since Julian Epoch noon).
- `compute_jd/1` and `compute_ymd/1` mirror `computeJD` / `computeYMD`.
- `julianday('2000-01-01') == 2451544.5` (iJD = 211_885_387_200_000).
- Invalid inputs return `nil` exactly as SQLite returns NULL.
Public surface: `date/1`, `time/1`, `datetime/1`, `julianday/1`,
`unixepoch/1`, `strftime/1`, `timediff/1`. Each receives the already-evaluated
argument list (Elixir values).
"""
import Bitwise
# ── public entry-points ───────────────────────────────────────────────────
@doc "SQLite `date(timestring, mod...)`"
def date(args) do
case parse_args(args) do
{:ok, dt} -> format_ymd(dt)
:error -> nil
end
end
@doc "SQLite `time(timestring, mod...)`"
def time(args) do
case parse_args(args) do
{:ok, dt} -> format_hms(dt)
:error -> nil
end
end
@doc "SQLite `datetime(timestring, mod...)`"
def datetime(args) do
case parse_args(args) do
{:ok, dt} -> format_datetime(dt)
:error -> nil
end
end
@doc "SQLite `julianday(timestring, mod...)`"
def julianday(args) do
case parse_args(args) do
{:ok, dt} -> dt.ijd / 86_400_000.0
:error -> nil
end
end
@doc "SQLite `unixepoch(timestring, mod...)`"
def unixepoch(args) do
case parse_args(args) do
{:ok, dt} -> div(dt.ijd, 1000) - 210_866_760_000
:error -> nil
end
end
@doc "SQLite `strftime(format, timestring, mod...)`"
def strftime([nil | _]), do: nil
def strftime([fmt | args]) when is_binary(fmt) do
if Enum.any?(args, &is_nil/1) do
nil
else
case parse_args(args) do
{:ok, dt} -> apply_strftime(fmt, dt)
:error -> nil
end
end
end
def strftime(_), do: nil
@doc "SQLite `timediff(a, b)`"
def timediff([a, b]) do
with false <- a == nil or b == nil,
{:ok, dt_a} <- parse_args([a]),
{:ok, dt_b} <- parse_args([b]) do
a2 = dt_a |> compute_jd() |> compute_ymd_hms()
b2 = dt_b |> compute_jd() |> compute_ymd_hms()
if a2.ijd >= b2.ijd do
format_timediff("+", timediff_forward(b2, a2))
else
format_timediff("-", timediff_backward(b2, a2))
end
else
_ -> nil
end
end
def timediff(_), do: nil
# ── dt struct ─────────────────────────────────────────────────────────────
#
# Mirrors the C `DateTime` struct. `ijd` is iJD (ms since Julian epoch).
defstruct ijd: 0,
y: 0,
mo: 0,
d: 0,
h: 0,
mi: 0,
s: 0.0,
valid_jd: false,
valid_ymd: false,
valid_hms: false,
raw_s: false,
is_utc: false,
n_floor: 0
# ── argument parsing ──────────────────────────────────────────────────────
defp parse_args(args) when is_list(args) do
if Enum.any?(args, &is_nil/1) do
:error
else
case args do
[] ->
{:ok, now_dt()}
[first | mods] ->
case parse_time_value(first) do
:error ->
:error
{:ok, dt} ->
case apply_modifiers(dt, mods, 1) do
:error ->
:error
{:ok, dt2} ->
dt3 = compute_jd(dt2)
if not valid_ijd?(dt3.ijd) do
:error
else
# Mirror isDate() in date.c: when there are no modifiers and
# the input is a YYYY-MM-DD with D > 28, clear validYMD so that
# the output is re-derived from the Julian Day (normalizing
# overflow like 2023-02-31 → 2023-03-03).
dt4 =
if mods == [] and dt3.valid_ymd and dt3.d > 28 do
%{dt3 | valid_ymd: false}
else
dt3
end
{:ok, dt4}
end
end
end
end
end
end
defp parse_args(_), do: :error
# ── parse first argument ──────────────────────────────────────────────────
# Numeric (integer or float): set rawS
defp parse_time_value(v) when is_integer(v) or is_float(v) do
r = v * 1.0
{:ok, set_raw_date_number(r)}
end
# String: try YYYY-MM-DD..., then HH:MM..., then "now", then numeric string
defp parse_time_value(v) when is_binary(v) do
s = String.trim_trailing(v)
cond do
result = parse_yyyy_mm_dd(s) ->
{:ok, result}
result = parse_hh_mm_ss_string(s) ->
{:ok, result}
String.downcase(s) == "now" ->
{:ok, now_dt()}
true ->
case Float.parse(s) do
{r, ""} -> {:ok, set_raw_date_number(r)}
_ -> :error
end
end
end
defp parse_time_value(_), do: :error
# rawS path: if value is in julian-day range, treat as JD; else store as s
defp set_raw_date_number(r) do
dt = %__MODULE__{s: r, raw_s: true}
if r >= 0.0 and r < 5_373_484.5 do
ijd = trunc(r * 86_400_000.0 + 0.5)
%{dt | ijd: ijd, valid_jd: true}
else
dt
end
end
# ── parse YYYY-MM-DD [separator] HH:MM[:SS[.SSS]] [timezone] ─────────────
defp parse_yyyy_mm_dd(s) do
case parse_date_part(s) do
{:ok, year, month, day, rest} when month >= 1 and month <= 12 and day >= 1 and day <= 31 ->
{h, mi, sec, tz_inline, is_utc_inline, rest2} = parse_time_part(rest)
# SQLite's parseTimezone only accepts one timezone specifier (Z or +/-HH:MM).
# If the inline parse already found a timezone, the remaining text must be
# only whitespace — anything else is an error.
trailing_tz =
if tz_inline != 0 or is_utc_inline do
# A timezone was already parsed inline; rest must be empty/whitespace.
if drop_spaces(rest2) == "" do
{0, false}
else
:error
end
else
parse_trailing_tz(rest2)
end
case trailing_tz do
:error ->
nil
{tz2, is_utc2} ->
tz_total = tz_inline + tz2
is_utc_final = is_utc_inline or is_utc2
dt = %__MODULE__{y: year, mo: month, d: day, valid_ymd: true}
dt =
if h != nil do
%{dt | h: h, mi: mi, s: sec, valid_hms: true}
else
dt
end
dt = compute_floor(dt)
if tz_total != 0 do
dt2 = compute_jd(dt)
%{
dt2
| ijd: dt2.ijd - tz_total * 60_000,
valid_ymd: false,
valid_hms: false,
is_utc: true
}
else
if is_utc_final, do: %{dt | is_utc: true}, else: dt
end
end
_ ->
nil
end
end
# Returns {:ok, year, month, day, rest} or :error
defp parse_date_part(s) do
case s do
<<y1, y2, y3, y4, ?-, m1, m2, ?-, d1, d2, rest::binary>>
when y1 in ?0..?9 and y2 in ?0..?9 and y3 in ?0..?9 and y4 in ?0..?9 and
m1 in ?0..?9 and m2 in ?0..?9 and d1 in ?0..?9 and d2 in ?0..?9 ->
year = (y1 - ?0) * 1000 + (y2 - ?0) * 100 + (y3 - ?0) * 10 + (y4 - ?0)
month = (m1 - ?0) * 10 + (m2 - ?0)
day = (d1 - ?0) * 10 + (d2 - ?0)
{:ok, year, month, day, rest}
_ ->
:error
end
end
# Strip optional separator (whitespace / T) then parse optional HH:MM[:SS[.SSS]]
# Returns {h, mi, sec, tz_inline, is_utc_inline, rest}
# h is nil if no time present
defp parse_time_part(rest) do
rest1 = rest |> drop_spaces() |> drop_t_sep() |> drop_spaces()
case parse_hh_mm_ss_raw(rest1) do
{:ok, h, mi, sec, tz_inline, is_utc, rest2} ->
{h, mi, sec, tz_inline, is_utc, rest2}
:error ->
{nil, 0, 0.0, 0, false, rest}
end
end
defp drop_spaces(<<?\s, rest::binary>>), do: drop_spaces(rest)
defp drop_spaces(s), do: s
defp drop_t_sep(<<t, rest::binary>>) when t == ?T or t == ?t, do: rest
defp drop_t_sep(s), do: s
# Parse HH:MM[:SS[.FFF]][tz], return {:ok, h, mi, s, tz, is_utc, rest} | :error
defp parse_hh_mm_ss_raw(s) do
case s do
<<h1, h2, ?:, m1, m2, rest::binary>>
when h1 in ?0..?9 and h2 in ?0..?9 and m1 in ?0..?9 and m2 in ?0..?9 ->
h = (h1 - ?0) * 10 + (h2 - ?0)
mi = (m1 - ?0) * 10 + (m2 - ?0)
if h > 24 or mi > 59 do
:error
else
case parse_ss_frac(rest) do
:error ->
:error
{sec, rest2} ->
{tz_inline, is_utc, rest3} = parse_tz_inline(rest2)
{:ok, h, mi, sec, tz_inline, is_utc, rest3}
end
end
_ ->
:error
end
end
# Parse optional :SS[.FFF], return {seconds, rest}
defp parse_ss_frac(<<?:, s1, s2, rest::binary>>) when s1 in ?0..?9 and s2 in ?0..?9 do
sec0 = (s1 - ?0) * 10 + (s2 - ?0)
if sec0 > 59 do
:error
else
case rest do
<<?.>> ->
# bare dot with no digits — invalid per SQLite
:error
<<?., d, rest2::binary>> when d in ?0..?9 ->
{frac, rest3} = parse_frac_digits(rest2, (d - ?0) * 1.0, 10.0)
# Truncate to avoid sub-ms rounding
frac2 = min(frac, 0.999)
{sec0 * 1.0 + frac2, rest3}
_ ->
{sec0 * 1.0, rest}
end
end
end
defp parse_ss_frac(rest), do: {0.0, rest}
defp parse_frac_digits(<<d, rest::binary>>, acc, scale) when d in ?0..?9 do
parse_frac_digits(rest, acc * 10.0 + (d - ?0), scale * 10.0)
end
defp parse_frac_digits(rest, acc, scale), do: {acc / scale, rest}
# Parse inline timezone attached to the HMS string
defp parse_tz_inline(s) do
s1 = drop_spaces(s)
case s1 do
<<c, rest::binary>> when c == ?Z or c == ?z ->
{0, true, drop_spaces(rest)}
<<sign, h1, h2, ?:, m1, m2, rest::binary>>
when sign in [?+, ?-] and h1 in ?0..?9 and h2 in ?0..?9 and m1 in ?0..?9 and
m2 in ?0..?9 ->
hr = (h1 - ?0) * 10 + (h2 - ?0)
mn = (m1 - ?0) * 10 + (m2 - ?0)
if mn > 59 do
{0, false, s}
else
offset = hr * 60 + mn
sgn = if sign == ?+, do: 1, else: -1
total = sgn * offset
{total, total == 0, drop_spaces(rest)}
end
_ ->
{0, false, s}
end
end
# Parse HH:MM[:SS[.SSS]] as a standalone string (no date prefix)
defp parse_hh_mm_ss_string(s) do
case parse_hh_mm_ss_raw(s) do
{:ok, h, mi, sec, _tz, _is_utc, rest} ->
if rest == "" or String.trim(rest) == "" do
%__MODULE__{
h: h,
mi: mi,
s: sec,
valid_hms: true,
# SQLite defaults date to 2000-01-01 for time-only strings
y: 2000,
mo: 1,
d: 1,
valid_ymd: true
}
else
nil
end
:error ->
nil
end
end
# Parse trailing timezone after parsing date+time (only Z or +/-HH:MM allowed here)
defp parse_trailing_tz(""), do: {0, false}
defp parse_trailing_tz(s) do
s1 = drop_spaces(s)
case s1 do
"" ->
{0, false}
<<c, rest::binary>> when c == ?Z or c == ?z ->
rest2 = drop_spaces(rest)
if rest2 == "", do: {0, true}, else: :error
<<sign, h1, h2, ?:, m1, m2, rest::binary>>
when sign in [?+, ?-] and h1 in ?0..?9 and h2 in ?0..?9 and m1 in ?0..?9 and
m2 in ?0..?9 ->
hr = (h1 - ?0) * 10 + (h2 - ?0)
mn = (m1 - ?0) * 10 + (m2 - ?0)
if mn > 59 do
:error
else
rest2 = drop_spaces(rest)
if rest2 != "" do
:error
else
offset = hr * 60 + mn
sgn = if sign == ?+, do: 1, else: -1
total = sgn * offset
{total, total == 0}
end
end
_ ->
:error
end
end
# current UTC time as iJD
defp now_dt do
utc = DateTime.utc_now()
unix_ms = DateTime.to_unix(utc, :millisecond)
# iJD = unix_ms + 2440587.5 * 86400000 = unix_ms + 210_866_760_000_000
ijd = unix_ms + 210_866_760_000_000
%__MODULE__{ijd: ijd, valid_jd: true, is_utc: true}
end
# ── modifiers ─────────────────────────────────────────────────────────────
defp apply_modifiers(dt, [], _idx), do: {:ok, dt}
defp apply_modifiers(dt, [mod | rest], idx) do
case apply_modifier(dt, mod, idx) do
:error -> :error
{:ok, dt2} -> apply_modifiers(dt2, rest, idx + 1)
end
end
defp apply_modifier(_dt, nil, _idx), do: :error
defp apply_modifier(_dt, mod, _idx) when not is_binary(mod), do: :error
defp apply_modifier(dt, mod, idx) do
m = String.trim(mod)
ml = String.downcase(m)
cond do
ml == "unixepoch" ->
apply_unixepoch(dt, idx)
ml == "julianday" ->
apply_julianday(dt, idx)
String.starts_with?(ml, "start of ") ->
apply_start_of(dt, String.slice(ml, 9, byte_size(ml) - 9) |> String.trim())
String.starts_with?(ml, "weekday") ->
apply_weekday(dt, String.slice(ml, 7, byte_size(ml) - 7) |> String.trim())
# localtime / utc are no-ops per task instructions
ml == "utc" or ml == "localtime" ->
{:ok, dt}
true ->
apply_offset_modifier(dt, m)
end
end
defp apply_unixepoch(dt, idx) do
if idx > 1 do
:error
else
if dt.raw_s do
r = dt.s * 1000.0 + 210_866_760_000_000.0
if r >= 0.0 and r < 464_269_060_800_000.0 do
ijd = trunc(r + 0.5)
dt2 = %{dt | ijd: ijd, valid_jd: true, raw_s: false, valid_ymd: false, valid_hms: false}
{:ok, dt2}
else
:error
end
else
:error
end
end
end
defp apply_julianday(dt, idx) do
if idx > 1 do
:error
else
if dt.valid_jd and dt.raw_s do
{:ok, %{dt | raw_s: false}}
else
:error
end
end
end
defp apply_start_of(dt, what) do
case what do
"month" ->
dt2 = compute_ymd(dt)
{:ok, %{dt2 | d: 1, h: 0, mi: 0, s: 0.0, valid_hms: true, valid_jd: false}}
"year" ->
dt2 = compute_ymd(dt)
{:ok, %{dt2 | mo: 1, d: 1, h: 0, mi: 0, s: 0.0, valid_hms: true, valid_jd: false}}
"day" ->
dt2 = compute_ymd(dt)
{:ok, %{dt2 | h: 0, mi: 0, s: 0.0, valid_hms: true, valid_jd: false}}
_ ->
:error
end
end
defp apply_weekday(dt, rest) do
case Float.parse(rest) do
{r, ""} when r >= 0.0 and r < 7.0 and trunc(r) == r ->
n = trunc(r)
# Must compute JD first, then compute week-day offset
dt2 = dt |> compute_ymd_hms() |> Map.put(:valid_jd, false) |> compute_jd()
# (iJD + 129600000) / 86400000 % 7 => day of week, 0=Sun (daysAfterSunday in date.c)
z = rem(div(dt2.ijd + 129_600_000, 86_400_000), 7)
z2 = if z > n, do: z - 7, else: z
ijd2 = dt2.ijd + (n - z2) * 86_400_000
{:ok, clear_ymd_hms(%{dt2 | ijd: ijd2})}
_ ->
:error
end
end
# Parse "+/-NNN unit" or "NNN unit" modifiers
defp apply_offset_modifier(dt, m) do
{sign_char, rest} =
case m do
<<c, r::binary>> when c in [?+, ?-] -> {c, r}
_ -> {?+, m}
end
case Float.parse(rest) do
{r_abs, rest2} ->
r = if sign_char == ?-, do: -r_abs, else: r_abs
unit = rest2 |> String.trim() |> String.downcase()
# Strip trailing 's' for plural forms
unit2 = if String.ends_with?(unit, "s"), do: String.slice(unit, 0..-2//1), else: unit
case unit2 do
"second" -> add_seconds(dt, r)
"minute" -> add_seconds(dt, r * 60.0)
"hour" -> add_seconds(dt, r * 3600.0)
"day" -> add_seconds(dt, r * 86_400.0)
"month" -> add_months(dt, r)
"year" -> add_years(dt, r)
_ -> :error
end
:error ->
:error
end
end
defp add_seconds(dt, r) do
rr = if r < 0, do: -0.5, else: 0.5
dt2 = compute_jd(dt)
ijd2 = dt2.ijd + trunc(r * 1000.0 + rr)
{:ok, clear_ymd_hms(%{dt2 | ijd: ijd2, n_floor: 0})}
end
defp add_months(dt, r) do
dt2 = compute_ymd_hms(dt)
int_r = trunc(r)
mo2 = dt2.mo + int_r
{y2, mo3} = normalize_month(dt2.y, mo2)
dt3 = compute_floor(%{dt2 | y: y2, mo: mo3, valid_jd: false})
frac = r - int_r
dt4 = compute_jd(dt3)
rr = if frac < 0, do: -0.5, else: 0.5
ijd2 = dt4.ijd + trunc(frac * 2_592_000_000.0 + rr)
{:ok, clear_ymd_hms(%{dt4 | ijd: ijd2})}
end
defp add_years(dt, r) do
dt2 = compute_ymd_hms(dt)
int_r = trunc(r)
dt3 = compute_floor(%{dt2 | y: dt2.y + int_r, valid_jd: false})
frac = r - int_r
dt4 = compute_jd(dt3)
rr = if frac < 0, do: -0.5, else: 0.5
ijd2 = dt4.ijd + trunc(frac * 31_536_000_000.0 + rr)
{:ok, clear_ymd_hms(%{dt4 | ijd: ijd2})}
end
defp timediff_forward(base, target) do
max_months = max((target.y - base.y) * 12 + target.mo - base.mo, 0)
{months, shifted} = forward_month_anchor(base, target, max_months)
split_interval(months, target.ijd - shifted.ijd)
end
defp forward_month_anchor(base, target, months) do
shifted = shift_months!(base, months)
if shifted.ijd > target.ijd and months > 0 do
forward_month_anchor(base, target, months - 1)
else
{months, shifted}
end
end
defp timediff_backward(later, earlier) do
max_months = max((later.y - earlier.y) * 12 + later.mo - earlier.mo, 0)
{months, shifted} = backward_month_anchor(later, earlier, max_months)
split_interval(months, shifted.ijd - earlier.ijd)
end
defp backward_month_anchor(later, earlier, months) do
shifted = shift_months!(later, -months)
if shifted.ijd < earlier.ijd and months > 0 do
backward_month_anchor(later, earlier, months - 1)
else
{months, shifted}
end
end
defp shift_months!(dt, months) do
{:ok, shifted} = add_months(dt, months)
shifted |> compute_jd() |> compute_ymd_hms()
end
defp split_interval(months, ms) do
years = div(months, 12)
rem_months = rem(months, 12)
days = div(ms, 86_400_000)
rem_ms = ms - days * 86_400_000
hours = div(rem_ms, 3_600_000)
rem_ms = rem_ms - hours * 3_600_000
minutes = div(rem_ms, 60_000)
rem_ms = rem_ms - minutes * 60_000
seconds = div(rem_ms, 1000)
millis = rem_ms - seconds * 1000
{years, rem_months, days, hours, minutes, seconds, millis}
end
defp format_timediff(sign, {years, months, days, hours, minutes, seconds, millis}) do
sign <>
pad4(years) <>
"-" <>
pad2(months) <>
"-" <>
pad2(days) <>
" " <>
pad2(hours) <>
":" <>
pad2(minutes) <>
":" <>
pad2(seconds) <>
"." <>
pad3(millis)
end
# Normalize month into 1..12, adjusting year.
# Mirrors the x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12 logic in date.c.
defp normalize_month(y, mo) do
x =
if mo > 0 do
div(mo - 1, 12)
else
div(mo - 12, 12)
end
{y + x, mo - x * 12}
end
# ── Julian Day conversion (computeJD in date.c, Meeus p.61) ──────────────
defp compute_jd(%__MODULE__{valid_jd: true} = dt), do: dt
defp compute_jd(%__MODULE__{raw_s: true} = dt) do
# rawS without validJD means the date is out of JD range — leave as-is
dt
end
defp compute_jd(%__MODULE__{} = dt) do
{y, mo, d} =
if dt.valid_ymd do
{dt.y, dt.mo, dt.d}
else
{2000, 1, 1}
end
if y < -4713 or y > 9999 or dt.raw_s do
# Out of range — mark as invalid (ijd=0, valid_jd=false)
%{dt | ijd: 0, valid_jd: false}
else
{y2, mo2} = if mo <= 2, do: {y - 1, mo + 12}, else: {y, mo}
a = div(y2 + 4800, 100)
b = 38 - a + div(a, 4)
x1 = div(36_525 * (y2 + 4716), 100)
x2 = div(306_001 * (mo2 + 1), 10_000)
ijd_base = trunc((x1 + x2 + d + b - 1524.5) * 86_400_000)
ijd =
if dt.valid_hms do
ijd_base + dt.h * 3_600_000 + dt.mi * 60_000 + trunc(dt.s * 1000 + 0.5)
else
ijd_base
end
%{dt | ijd: ijd, valid_jd: true}
end
end
# ── YMD from JD (computeYMD in date.c) ───────────────────────────────────
defp compute_ymd(%__MODULE__{valid_ymd: true} = dt), do: dt
defp compute_ymd(%__MODULE__{valid_jd: false} = dt) do
%{dt | y: 2000, mo: 1, d: 1, valid_ymd: true}
end
defp compute_ymd(%__MODULE__{} = dt) do
# Mirrors computeYMD in date.c exactly, including float arithmetic.
z = div(dt.ijd + 43_200_000, 86_400_000)
alpha = trunc((z + 32_044.75) / 36_524.25) - 52
a = z + 1 + alpha - div(alpha + 100, 4) + 25
b = a + 1524
c = trunc((b - 122.1) / 365.25)
dd = div(36_525 * band(c, 32_767), 100)
e = trunc((b - dd) / 30.6001)
x1 = trunc(30.6001 * e)
d = b - dd - x1
mo = if e < 14, do: e - 1, else: e - 13
y = if mo > 2, do: c - 4716, else: c - 4715
%{dt | y: y, mo: mo, d: d, valid_ymd: true}
end
# ── HMS from JD (computeHMS in date.c) ───────────────────────────────────
defp compute_hms(%__MODULE__{valid_hms: true} = dt), do: dt
defp compute_hms(%__MODULE__{} = dt) do
dt2 = compute_jd(dt)
day_ms = rem(dt2.ijd + 43_200_000, 86_400_000)
sec = rem(day_ms, 60_000) / 1000.0
day_min = div(day_ms, 60_000)
mi = rem(day_min, 60)
h = div(day_min, 60)
%{dt2 | h: h, mi: mi, s: sec, raw_s: false, valid_hms: true}
end
defp compute_ymd_hms(dt), do: dt |> compute_ymd() |> compute_hms()
defp clear_ymd_hms(dt), do: %{dt | valid_ymd: false, valid_hms: false}
# ── day-of-month overflow (computeFloor in date.c) ────────────────────────
#
# Bit pattern 0x15AA has bits set for months with 31 days:
# months 1,3,5,7,8,10,12 => bits 1,3,5,7,8,10,12 set.
defp compute_floor(%__MODULE__{valid_ymd: true} = dt) do
n =
cond do
dt.d <= 28 ->
0
band(1 <<< dt.mo, 0x15AA) != 0 ->
0
dt.mo != 2 ->
if dt.d == 31, do: 1, else: 0
leap_year?(dt.y) ->
max(dt.d - 29, 0)
true ->
max(dt.d - 28, 0)
end
%{dt | n_floor: n}
end
defp compute_floor(dt), do: dt
defp leap_year?(y), do: rem(y, 4) == 0 and (rem(y, 100) != 0 or rem(y, 400) == 0)
# ── validation ────────────────────────────────────────────────────────────
# Maximum valid iJD: 9999-12-31 23:59:59.999 = 464_269_060_799_999 ms
@max_ijd 464_269_060_799_999
defp valid_ijd?(ijd), do: ijd >= 0 and ijd <= @max_ijd
# ── output formatters ─────────────────────────────────────────────────────
defp format_ymd(dt) do
dt2 = compute_ymd(dt)
y = abs(dt2.y)
pad4(y) <> "-" <> pad2(dt2.mo) <> "-" <> pad2(dt2.d)
end
defp format_hms(dt) do
dt2 = compute_hms(dt)
s = trunc(dt2.s)
pad2(dt2.h) <> ":" <> pad2(dt2.mi) <> ":" <> pad2(s)
end
defp format_datetime(dt) do
dt2 = dt |> compute_ymd() |> compute_hms()
y = abs(dt2.y)
s = trunc(dt2.s)
pad4(y) <>
"-" <>
pad2(dt2.mo) <>
"-" <>
pad2(dt2.d) <>
" " <>
pad2(dt2.h) <>
":" <>
pad2(dt2.mi) <>
":" <>
pad2(s)
end
# ── strftime ─────────────────────────────────────────────────────────────
defp apply_strftime(fmt, dt) do
dt2 = dt |> compute_ymd() |> compute_hms()
do_strftime(fmt, dt2, "")
end
defp do_strftime("", _dt, acc), do: acc
defp do_strftime(<<?%, c, rest::binary>>, dt, acc) do
case strftime_sub(c, dt) do
nil ->
# Unrecognized format specifier → return NULL (as SQLite does).
nil
sub ->
do_strftime(rest, dt, acc <> sub)
end
end
defp do_strftime(<<c, rest::binary>>, dt, acc) do
do_strftime(rest, dt, acc <> <<c>>)
end
defp strftime_sub(?d, dt), do: pad2(dt.d)
defp strftime_sub(?e, dt) do
String.pad_leading(Integer.to_string(dt.d), 2)
end
defp strftime_sub(?H, dt), do: pad2(dt.h)
defp strftime_sub(?k, dt) do
String.pad_leading(Integer.to_string(dt.h), 2)
end
defp strftime_sub(?I, dt) do
h = dt.h
h2 = if h > 12, do: h - 12, else: h
h3 = if h2 == 0, do: 12, else: h2
pad2(h3)
end
defp strftime_sub(?l, dt) do
h = dt.h
h2 = if h > 12, do: h - 12, else: h
h3 = if h2 == 0, do: 12, else: h2
String.pad_leading(Integer.to_string(h3), 2)
end
defp strftime_sub(?p, dt), do: if(dt.h >= 12, do: "PM", else: "AM")
defp strftime_sub(?P, dt), do: if(dt.h >= 12, do: "pm", else: "am")
defp strftime_sub(?f, dt) do
# Fractional seconds: SS.SSS (mirrors SQLite's %06.3f)
s = min(dt.s, 59.999)
int_s = trunc(s)
frac = s - int_s
# Round to ms, cap at 999
frac_ms = min(trunc(frac * 1000.0 + 0.5), 999)
pad2(int_s) <> "." <> pad3(frac_ms)
end
defp strftime_sub(?F, dt) do
pad4(abs(dt.y)) <> "-" <> pad2(dt.mo) <> "-" <> pad2(dt.d)
end
defp strftime_sub(?j, dt) do
# Day of year 001-366. Mirrors daysAfterJan01.
dt2 = compute_jd(dt)
jan01 = %{dt2 | d: 1, mo: 1, valid_jd: false}
jan01_jd = compute_jd(jan01)
day_num = div(dt2.ijd - jan01_jd.ijd + 43_200_000, 86_400_000) + 1
pad3(day_num)
end
defp strftime_sub(?J, dt) do
val = dt.ijd / 86_400_000.0
# Mirrors SQLite's %.16g C printf format: up to 16 significant digits,
# trailing zeros stripped, fixed notation when -4 <= exp < 16.
format_g16(val)
end
defp strftime_sub(?m, dt), do: pad2(dt.mo)
defp strftime_sub(?M, dt), do: pad2(dt.mi)
defp strftime_sub(?R, dt), do: pad2(dt.h) <> ":" <> pad2(dt.mi)
defp strftime_sub(?T, dt), do: pad2(dt.h) <> ":" <> pad2(dt.mi) <> ":" <> pad2(trunc(dt.s))
defp strftime_sub(?s, dt) do
# Seconds since Unix epoch 1970-01-01 00:00:00 UTC
unix_s = div(dt.ijd, 1000) - 210_866_760_000
Integer.to_string(unix_s)
end
defp strftime_sub(?S, dt), do: pad2(trunc(dt.s))
defp strftime_sub(?w, dt) do
Integer.to_string(days_after_sunday(dt))
end
defp strftime_sub(?u, dt) do
w = days_after_sunday(dt)
Integer.to_string(if w == 0, do: 7, else: w)
end
defp strftime_sub(?W, dt) do
# Week number 00-53; first Monday of year starts week 01.
# Mirrors (daysAfterJan01 - daysAfterMonday + 7) / 7.
dt2 = compute_jd(dt)
jan01 = %{dt2 | d: 1, mo: 1, valid_jd: false}
jan01_jd = compute_jd(jan01)
days_in_year = div(dt2.ijd - jan01_jd.ijd + 43_200_000, 86_400_000)
days_after_mon = days_after_monday(dt2)
wk = div(days_in_year - days_after_mon + 7, 7)
pad2(wk)
end
defp strftime_sub(?Y, dt), do: pad4(abs(dt.y))
defp strftime_sub(?%, _dt), do: "%"
defp strftime_sub(_, _dt), do: nil
# Days after Sunday (0=Sun, 1=Mon, …, 6=Sat). Mirrors daysAfterSunday.
defp days_after_sunday(dt) do
rem(div(dt.ijd + 129_600_000, 86_400_000), 7)
end
# Days after Monday (0=Mon, …, 6=Sun). Mirrors daysAfterMonday.
defp days_after_monday(dt) do
rem(div(dt.ijd + 43_200_000, 86_400_000), 7)
end
# ── padding helpers ───────────────────────────────────────────────────────
defp pad2(n), do: Integer.to_string(n) |> String.pad_leading(2, "0")
defp pad3(n), do: Integer.to_string(n) |> String.pad_leading(3, "0")
defp pad4(n), do: Integer.to_string(n) |> String.pad_leading(4, "0")
# Mirrors C's %.16g: up to 16 significant digits, trailing zeros stripped,
# uses fixed notation when -4 <= exponent < 16 (same as printf %g rules).
defp format_g16(val) when val == 0.0, do: "0"
defp format_g16(val) do
abs_val = abs(val)
exp = abs_val |> :math.log10() |> :math.floor() |> trunc()
if exp >= -4 and exp < 16 do
decimal_places = max(0, 15 - exp)
s = :erlang.float_to_binary(val, decimals: decimal_places)
if String.contains?(s, ".") do
s |> String.trim_trailing("0") |> String.trim_trailing(".")
else
s
end
else
:io_lib.format(~c"~.16g", [val]) |> IO.iodata_to_binary()
end
end
end