Direct answer

Time-indexed and depth-indexed drilling data should remain separate authoritative views connected by explicit, reproducible transformations. Time answers when, how long, and in what sequence. Depth answers where in the hole and across which footage. A safe analytical join states its direction, tolerance, eligibility rules, and provenance.

The wrong approach is to treat measured depth as a timestamp-like key, apply an unrestricted nearest match, and backfill whatever is missing. That produces complete-looking data while attaching context that may be stale, future, or operationally incompatible.

What each clock preserves

Time-based data is the natural record for:

  • one-second sensor streams;
  • rig-state transitions;
  • connection and trip duration;
  • alarms and event windows;
  • surface/downhole response lag;
  • data freshness and sequence.

Depth-based data is the natural record for:

  • formation and hole-section context;
  • survey and trajectory stations;
  • BHA/run performance through footage;
  • offset-well comparison;
  • plan-versus-actual analysis;
  • construction elements such as casing and tops.

Neither view is complete on its own. A depth plot without time hides duration and repeated work. A time plot without depth hides where the operation occurred.

Why measured depth repeats

During steady drilling, hole depth usually advances and a time-to-depth mapping can appear simple. Real operations immediately complicate it:

  • a connection creates many time samples at nearly constant hole depth;
  • reaming revisits an existing interval;
  • a trip moves bit depth while hole depth remains fixed;
  • backreaming moves bit depth in the opposite direction;
  • slide and rotate intervals can overlap the same nominal depth bucket;
  • surface systems can resend or correct records;
  • sidetracks can reuse measured-depth ranges on different branches.

“One row per foot” is therefore an analytical product, not raw truth.

Start with an explicit analytical question

The transformation should follow the question.

For a drilling ROP comparison, eligible observations may require an on-bottom drilling state and positive footage progression. For a hole-cleaning review, circulation and reaming states may be central rather than excluded. For an event investigation, retaining the full time sequence is more important than creating a depth average.

Define these four elements before joining:

  1. Grain: one-second row, event, stand, foot, survey station, run, or section.
  2. Eligibility: allowed states, quality flags, and source freshness.
  3. Alignment: exact, backward, forward, interval containment, or interpolation.
  4. Tolerance: the maximum distance in time or depth before the correct answer becomes “no match.”

ASOF joins need direction and tolerance

A backward ASOF join asks: “What was the most recent known context at or before this observation?” This is often appropriate for a BHA, active plan version, or rig state—but only within a bounded tolerance.

Conceptually:

aligned = merge_asof(
    observations.sort_values("event_time"),
    context.sort_values("effective_time"),
    left_on="event_time",
    right_on="effective_time",
    direction="backward",
    tolerance=allowed_age,
)

The tolerance is part of the engineering definition. A context record that is seconds old may be acceptable for one-second surface data. A survey can remain authoritative across a larger MD interval, but the result should still retain the source survey stations and mark interpolated positions.

Unrestricted backfilling is particularly dangerous because it can attach a later context to an earlier observation. Completeness improves while temporal integrity worsens.

Preserve the method, not only the joined value

Every derived row should retain enough information to audit the alignment:

Field Purpose
source timestamp or MD Identifies the actual contributing record.
aligned timestamp or MD Identifies the target analytical position.
alignment distance Exposes how far the source was carried.
method Exact, backward, interval, or interpolated.
tolerance States the maximum acceptable gap.
source quality Prevents low-quality values from becoming authoritative context.
calculation version Reproduces the transformation later.

That metadata does not need to clutter every chart. It must be available through an evidence view, export, or API response.

Survey interpolation has an authority boundary

Minimum-curvature interpolation is useful for estimating position between survey stations and aligning trajectory context with other depth records. It does not create a measured survey.

A safe trajectory record distinguishes:

  • original survey stations;
  • interpolated stations;
  • the source station pair;
  • the interpolation method;
  • whether extrapolation was permitted;
  • the active wellbore and plan version.

The display can draw a smooth trajectory while the evidence layer preserves which points were measured and which were derived.

Aggregating time into depth

Consider a synthetic one-foot bucket that contains 30 seconds of rotary drilling, 20 seconds off bottom, and a brief ROP spike. A simple mean combines incompatible states. A final-value rule may capture the off-bottom period. A maximum may preserve the spike but exaggerate typical performance.

A more defensible output could include:

  • eligible drilling seconds;
  • drilled footage;
  • median and percentile range;
  • state composition;
  • minimum and maximum envelope;
  • number of source samples;
  • quality coverage.

The chart can choose one display statistic, but the underlying analytical record should keep the denominators and distribution.

Reconciling live and historical results

Live dashboards often query a recent operational store while historical analysis reads a columnar archive. Late arrivals and recalculations can change the answer after the operation.

Use a declared handoff:

  1. Stamp event time and ingest time.
  2. Deduplicate by stable source identity.
  3. Mark the live/archive cutoff.
  4. Reprocess calculated channels by version.
  5. Record data-as-of in every result.
  6. Compare overlapping live and archive windows before retiring the hot copy.

An engineer should be able to tell whether a result is preliminary live context or reconciled history.

Implementation checklist

  • Keep event time, ingest time, hole depth, bit depth, and wellbore identity distinct.
  • Define grain and eligible rig states for each metric.
  • Use directional joins with explicit tolerances.
  • Return no match when the tolerance is exceeded.
  • Never label interpolated geometry as a measured survey.
  • Preserve sample counts, exposure, and state composition in depth aggregates.
  • Version calculations and report data-as-of.
  • Test repeated depth, reverse movement, missing context, sidetracks, late data, and unit changes.

Time and depth are not competing representations. Together they let a drilling platform explain both the operational sequence and the physical progression of the well.

Related resources