Navigating temporal offsets: why Amber counts days and nanoseconds from 2000-01-01

Here is a chart that looks fine.

It has a time axis. The axis has dates on it. The line moves the way a price moves. Nothing about it is visibly wrong, and it will pass every review it is ever shown in. The only problem is that every point on it is thirty years and a few days from where it belongs, and nobody will notice until someone tries to join it against a table that got the offset right.

This is the failure mode that epoch mismatches actually have. They do not crash. They do not throw. They produce plausible output, which is the worst thing a bug can do.

What Amber actually stores

Three temporal types, three integers.

They are first-class types with literal syntax and type-aware arithmetic — date - date gives you days, time + time gives you a time, year and dow and hh work on them directly. But the storage is numeric, exactly as kdb does internally, which is what lets xasc and the `s attribute work on a time column unchanged and lets the as-of kernel binary-search it.

And the three constants that follow from the epoch choice are:

Every conversion in this post is one of those three, applied in one of two directions. There is nothing else to it, and that is precisely why it is dangerous — a mistake this simple does not feel like it needs checking.

Why not just use Unix time?

Three reasons, in descending order of how much they matter.

1. Range, at the end you care about

An int64 nanosecond counter spans ±292.47 years. Where you put the zero decides which 585 years you get.

Financial data is a forward-looking problem. Long-dated instruments, retirement products, sovereign issues and structured notes routinely reference dates that a 2262 ceiling makes awkward and a 2292 ceiling does not. Giving up 1677–1707 to buy 2262–2292 is not a close call: there is no tick data from 1690.

This is not hypothetical. pandas' default datetime64[ns] has exactly the 2262 ceiling, and it is a real operational nuisance in fixed income.

2. Smaller numbers, denser columns

Amber stores an integer vector in the narrowest type that holds every element. A date column for a contemporary trading session holds values in the nine-thousands from a 2000 epoch, and in the twenty-thousands from a 1970 one. Both fit int16, so for dates this is a small win — but the same principle runs through the whole design, and combined with symbol enumeration (four bytes a row rather than a string) it is a large part of why a tick table is as compact as it is.

3. It is the convention the domain already uses

kdb+ uses the same epoch and the same millisecond-of-day convention for time. That means a tickerplant query ported from q means the same thing here without rebasing anything, and a stored kdb-shaped column can be read as-is. When your users are people who already have twenty years of q muscle memory, matching that convention is worth more than matching POSIX.

The two magnitudes that look alike

Here is the part that turns an offset into a bug rather than a conversion.

A column of milliseconds of day holds values from 0 to 86,399,999 — eight digits. A column of nanoseconds since 2000 for a market open holds values around 838,000,000,000,000 — fifteen digits. Those are far apart, so you might think you could just look.

But a column of seconds since 2000 is nine digits, a column of milliseconds since 2000 is thirteen, and a column of microseconds of day is eleven. Once you are handling data from more than one source, the magnitudes overlap in ways that no heuristic gets right every time, and the value that gets it wrong is not the one you eyeballed.

How each boundary handles it

Grafana

Grafana's time axis is Unix milliseconds. The Go backend converts, and the unit is a datasource setting so a new panel is right before anyone touches it:

jsonData:
  host: amberd
  port: 5012
  # The store's `time` columns are nanoseconds since 2000-01-01 -- Amber's own
  # timestamp epoch, not the Unix one. Every panel sets this explicitly too, but
  # the default belongs here so a new panel is right before anyone touches it.
  defaultTimeUnit: ns-2000

And the query macros come in both flavours, named so that picking the wrong one is a visible choice rather than an omission:

The temporal conversion has its own boundary tests in pkg/amberd, which uses nothing but Go's standard library — so an off-by-an-epoch reproduces in go test with no Grafana anywhere near it.

pandas and NumPy

This is the one where the temptation to be helpful is strongest, and where being helpful would be wrong.

Table.from_pandas maps datetime64 columns to int64 nanoseconds without re-basing the epoch. It would be easy to add the constant. It would also mean that somewhere in your pipeline, a number silently changed by thirty years, and the only record of it is a line in a library's documentation you did not read.

So the conversion is yours, and it is visible:

import numpy as np, pandas as pd, amber as am

AMBER_EPOCH_NS = 946_684_800_000_000_000       # 2000-01-01T00:00:00Z

# Amber timestamps -> pandas
t   = am.q("select time, px from trades")
ns  = np.asarray(t.column("time", raw=True))   # zero-copy int64 view
idx = pd.to_datetime(ns + AMBER_EPOCH_NS, utc=True)

# pandas -> Amber timestamps
back = df.index.view("int64") - AMBER_EPOCH_NS
am.set("time", back.astype("int64"))

Name the constant. A bare 946684800000000000 in the middle of an expression is unreviewable; AMBER_EPOCH_NS is not.

Arrow

Arrow's timestamp type carries its epoch in the schema, and that epoch is always 1970. So a column exported as timestamp("ns") holding Amber counts would be a schema that lies, and every downstream consumer would believe it.

The export therefore leaves the column typed as an integer unless you say otherwise. An honest int64 is better than a mistyped timestamp:

import pyarrow as pa, amber_arrow as aa

tbl = aa.query("select time, px from trades").read_all()
ts  = pa.compute.add(tbl.column("time"), 946_684_800_000_000_000)
tbl = tbl.set_column(0, pa.field("time", pa.timestamp("ns", tz="UTC")),
                     ts.cast(pa.timestamp("ns", tz="UTC")))

The store on disk

amber-tick writes time columns as int64 nanoseconds since 2000-01-01, and the partition directory name is the date. The Arrow IPC copy of the store is written in the same pass from the same arrays, so the two views cannot drift apart — a class of bug that "we'll regenerate the Arrow files nightly" would have introduced.

store/
  sym                       the symbol enumeration domain
  par.txt                   the partition list
  2026.08.21/               <- the partition name IS the date
    trades/{.d,sym,time,px,sz,side,venue,cond,seq,extime}
    quotes/{.d,sym,time,bid,ask,bsz,asz,bex,aex,cond}
  arrow/2026.08.21/*.arrow  the same data as Arrow IPC

Rendering is handled separately from storage: q/tick.k teaches amber.k's grid formatter about the store's timestamps without touching amber.k. The formatter change is presentational; the bytes on disk are unchanged and still binary-searchable.

Working with them in the language

None of the above is visible day to day, because the types do the right thing:

2026.07.30                          / a date
2026.08.15 - 2026.07.30             / 16  (days)
2026.07.30 + 16                     / 2026.08.15
10:00:00.000 + 00:00:05.000         / 10:00:05.000
2026.07.30D09:30:00.000000000       / a timestamp

year 2026.07.30                     / 2026
dow  2026.07.30                     / day of week
"D"$"2026.12.25"                    / parse a date from a string
`i$2026.07.30                       / 9709 — the raw number, when you want it

The accessors map onto q's dotted forms one for one, with one rename worth knowing:

Bucketing, which is where most temporal code actually lives

Almost every question about a trading day is a question about buckets. Amber has three functions and they compose:

minbar[w; t]      / w-minute bar, returned as a time (ms)
bar[w; u; t]      / generic: w buckets of u ms — bar[5;60000;t] = 5-minute bars
xbar[w; x]        / plain bucketing of any integer column

The classic tickerplant OHLCV query, unchanged from how a q shop would write it:

tb: +@[+trade; ,`time; minbar[1]@]
qby[tb; `sym`time;
    `open`high`low`close`vol!({first x`px};{max x`px};{min x`px};{last x`px};{sum x`sz})]

Or, at the prompt, bare:

select vwap:wavg[sz;px] by time:1m xbar time from trades

A checklist

If you take one thing from this, take the list:

• Name the constant. AMBER_EPOCH_NS = 946_684_800_000_000_000, once, in a module. Never inline.
• Convert at the boundary, not in the middle. One place per direction, per integration.
• Never infer the unit from the magnitude. Make it a setting with a documented default.
• Assert one known instant. A single test that says market open on a known date is a known integer catches every offset bug there is, in one line.
• Be suspicious of a chart that looks right. Check one point against a source you trust. Thirty years is invisible on a zoomed-in intraday axis.

The reference version of this material, with the full accessor table and every conversion, is on the Temporal mechanics page. The store layout is described in amber-tick, and the Grafana time macros in the datasource guide.