01 / EVIDENCE AND SECURITY TEXT · LESSON 03

UTC investigation windows

Select a precisely defined half-open UTC time window without platform-specific date commands.

16 min · learn at your pace

Normalise before comparing

A timestamp is useful only with a timezone and a known format. Here the exporter has already normalised every timestamp to fixed-width YYYY-MM-DDTHH:MM:SSZ, with valid dates and no fractional seconds. Under that contract, lexical order matches time order. The same shortcut does not work for mixed offsets, varying precision or local daylight-saving times.

Set LC_ALL=C for deterministic byte ordering. Portable awk can compare these canonical strings directly; no GNU date -d or macOS-only date -j is needed. If your source is not canonical, use a proper datetime parser before applying this technique.

Include the start, exclude the end

A half-open interval includes its starting timestamp and excludes its ending timestamp. Adjacent hourly windows then partition events without double-counting the boundary. Write the rule explicitly: timestamp >= start && timestamp < end.

Preserve the original event ID and time in the selected output. An event outside the window has not been declared safe; it simply falls outside this query. A security report should distinguish that scope choice from a detection result. The practice data includes both edges and out-of-order records so accidental inclusive-end filtering is visible.

Try an example.sh
awk -F '\t' 'NR>1 {print $1, $2}' events.tsv
YOUR TURN

Put it into practice.

events.tsv contains validated id<TAB>timestamp<TAB>result records. Select FAIL records in [2026-11-01T09:00:00Z, 2026-11-01T10:00:00Z). Print header id<TAB>timestamp and selected ID/time pairs in input order. All timestamps are canonical UTC; do not change their spelling.

See an explained solution
One way to solve it.sh
export LC_ALL=C
printf 'id\ttimestamp\n'
awk -F '\t' '$1!="id" && $3=="FAIL" && $2>="2026-11-01T09:00:00Z" && $2<"2026-11-01T10:00:00Z" {printf "%s\t%s\n",$1,$2}' events.tsv

The two comparisons implement a half-open interval. Because the exporter guarantees valid fixed-width UTC, lexical comparison is sufficient here; arbitrary timestamp strings would need normalisation first.