THE GOLD STANDARD OF INFORMATION
A clock reading is not an elapsed time
A workshop booking says 23:50 to 00:10. The session lasted twenty minutes if the finish was the next morning and the clock did not change offset. The two clock readings alone do not establish either condition. A file can store both readings perfectly while leaving the duration unknown.
Parquet separates calendar dates from times of day. That separation is useful: a return date can stay a date, and a recurring opening hour need not become a timestamp. Problems begin when a reader sees only integers, or a migration quietly supplies a date, a time zone or a rounding rule that the original record never contained.
Use this guide to work out what a field can legitimately answer, test how two tools carry it, and decide what extra evidence is needed before producing a date bucket, a schedule or a duration.
Information subject guide · Learning that transfers into life · How X Works hub
Choose your reading route
Route 1 · Chapters 1–3: Read the field
Start with the four kinds of time
Identify the claim and decode day counts and clock units.
Route 2 · Chapters 4–6: Cross midnight safely
Work through dates and clock windows
Handle wrap, legacy annotations and comparisons without manufacturing missing context.
Route 3 · Chapters 7–9: Test a handover
Compare a scan with filters and transfer the method to a new reporting system.
Full chapter index · Independent worked case · Questions and practical checks
Chapter index
Read the field
1. Four questions that need different values
2. Decode dates with a signed day count
3. Decode a time of day without guessing its unit
CHAPTER 1 OF 9 · Read the field
1. Four questions that need different values
Ask what question the column was designed to answer. A calendar date answers which day. A time of day answers where on a clock face. An instant identifies a point on a timeline. A duration measures how much time elapsed. A date and a local clock reading can together label a civil time, but an instant still needs enough context to resolve that label.
Imagine a library export with due_date = 2028-02-29, desk_opens = 09:00, returned_at = an observed instant, and handling_seconds = 90. The due date is about a calendar rule. The opening time repeats. The return observation orders events. The handling time is a measurement. It would be reasonable for all four fields to appear on the same row. It would not be reasonable to infer that all four are measurements in seconds since the same origin.
The physical carrier tells a reader how to receive bits. The logical annotation gives those bits a declared interpretation. The business contract supplies any further meaning, such as which site’s calendar determines a due date. Parquet DATE uses a signed 32-bit day count from 1 January 1970. TIME counts units after midnight, with a declared precision and UTC-adjustment flag. Those are file rules, not descriptions of the institution’s policy.
Do not infer meaning from a plausible number. A plain integer 32,400 might be a clock at 09:00 in seconds, a duration of nine hours, or an unrelated count. And a time column with 32,400 stored under millisecond units would indicate 00:00:32.400. A name such as open_time helps a person read the schema but does not change what a decoder must do.
Try writing a one-sentence test for a column: ‘The value remains the same when I change my display zone.’ That may fit a due date or a local opening clock, but a timestamp may display differently while representing the same instant. If your test is instead ‘Subtracting two values gives elapsed seconds,’ a pair of time-of-day values cannot pass it without additional conditions.
CHAPTER 2 OF 9 · Read the field
2. Decode dates with a signed day count
Set the zero point first. DATE value 0 means 1970-01-01. The next calendar date is 1, and 1969-12-31 is −1. The minus sign matters. Treating −1 as an unsigned 32-bit number turns a one-day step backwards into a number above four billion. Rejecting all negative dates, on the other hand, would wrongly reject valid historical records.
A day count enumerates dates; it does not measure the elapsed hours between two local midnights. A daylight-saving shift can make those two questions produce different numerical answers. In an attendance table, three calendar dates can still be three dates even when a clock change happens in between. Do not attach a zone merely to format the date for a screen.
For a calendar-boundary exercise, suppose a file carries the three successive dates 2000-02-28, 2000-02-29 and 2000-03-01. The differences in the day counts are 1 and 1. A reader that uses a simplistic February rule may skip the middle label. Now test 1900-02-28 to 1900-03-01: these are consecutive dates in the Gregorian calendar, because 1900 is not a leap year but 2000 is. A few ordinary summer dates would not reveal the calendar bug.
Two views of a source record can both be correct. A date displayed as 29 February 2028 can be stored as a day count rather than as those letters. A renderer may use a local language or rearrange year, month and day without changing the selected day. A timestamp display, by contrast, may move the date label across a day boundary when a time zone is applied. A test that compares only the rendered strings should distinguish display changes from actual changes in meaning.
For historical inputs, confirm which source calendar the values represent. A human-entered date written in another calendar is not made into a Gregorian date by labelling the column DATE. Likewise, a library may refuse to render a mathematically representable far-future day count because its date object has a narrower range. Separate validity under the file type from the supported range in the consumer.
CHAPTER 3 OF 9 · Read the field
3. Decode a time of day without guessing its unit
Take 09:02:03.456. Work in whole milliseconds first. Nine hours contribute 32,400,000; two minutes contribute 120,000; three seconds contribute 3,000; and the fraction adds 456. The total is 32,523,456. Under the same clock meaning this becomes 32,523,456,000 microseconds. The factor of a thousand changes the representation, not the time.
Parquet TIME with millisecond precision uses INT32. The microsecond and nanosecond forms use INT64. A normal 24-hour clock range for a teaching fixture runs from zero inclusive to the next midnight exclusive. Thus 86,399,999 milliseconds is 23:59:59.999, while 86,400,000 is already the next day’s zero, not another clock reading in the same range.
Now go the other way. A source value of 32,523,456,789 microseconds can be divided by 1,000 to obtain a quotient of 32,523,456 and remainder 789. Milliseconds alone cannot preserve that remainder. A lossless conversion has to reject the downcast or keep another representation. A chosen nearest-millisecond policy gives 32,523,457 in this example; truncation gives 32,523,456. Record which rule the task permits rather than letting a library choose silently.
Test the policy near the top of the day. Rounding 23:59:59.999600 to the nearest millisecond would reach the next midnight. If the output is still only a time of day, writing zero without any day carry loses information. If the output contains a date, it still needs a justified date increment. A service can instead reject values that cross a boundary or preserve microseconds. Those are different contracts.
A good exactness test includes zero, a typical time, a nonzero subunit remainder and the final representable tick before midnight. If all the examples are at whole seconds, incorrect conversion can pass every check. Use deliberately uncomfortable values to reveal what an apparently simple cast is doing.
CHAPTER 4 OF 9 · Cross midnight safely
4. Treat midnight wrap as an extra condition
Consider start = 23:50 and finish = 00:10 in local clock terms. Their millisecond counts are 85,800,000 and 600,000. The ordinary subtraction gives −85,200,000 milliseconds, or −23 hours 40 minutes. It does not establish that the observation was impossible. It tells us that the raw labels are not a continuous elapsed timeline.
If the intended rule is ‘finish is on the next date, with no offset transition’, add 86,400,000 to the finish count. The result, 1,200,000 milliseconds, is twenty minutes. That arithmetic is valid under the stated rule. It would be wrong if the recorded finish actually occurred two days later, or if the times were merely the scheduled slots rather than the observed start and end.
A scheduling rule may deliberately be circular. For a night desk open from 22:00 inclusive to 02:00 exclusive, a local clock check can be expressed as time at least 22:00 or time before 02:00. Writing a single ordinary interval from 22:00 to 02:00 gives an empty range. Test 21:59, 22:00, 00:00, 01:59 and 02:00. Only the middle three should pass.
The equal-boundary case needs a separate decision. A start and finish both at 07:00 can mean a zero-length window, a full-day window or an incomplete record. The clock values cannot choose between those meanings. Store an explicit policy or status rather than encoding it as an accident of which comparison operator you used.
A wrap check serves recurring coverage, not chronology. The same scheduled 01:30 can occur on many dates; on an offset transition date it might map differently than on an ordinary date. If the question is how long a real event lasted, compare appropriately resolved observations and keep the source calendar and clock labels if they are evidence you may need later.
CHAPTER 5 OF 9 · Cross midnight safely
5. Do not let legacy labels choose a time zone
TIME includes isAdjustedToUTC in its modern logical declaration. A local time-of-day field can preserve the clock label without resolving it to an instant. A UTC-adjusted time still does not have a date, so it too is insufficient to identify a unique occurrence. Reading ‘UTC’ as ‘full timestamp’ supplies information that the field does not contain.
Compatibility is a distinct concern. The Parquet logical-type specification describes the older TIME_MILLIS and TIME_MICROS converted labels. It gives mappings under which a local modern TIME declaration can also be written with a legacy converted label. An old reader that understands only that label may not distinguish what a modern reader can. A footer inspector that prints only the legacy string is not sufficient evidence that the modern flag was absent.
Build two teaching records with identical local-looking clock values but distinct modern flags. Write down the exact schema of each before the consumer sees it. A compatibility test should establish not only whether both files open but whether the consumer exposes enough metadata to identify their intended distinction. If the flags vanish in a CSV handover, a sidecar schema or a more faithful destination may be needed.
Do not convert a recurring local opening hour into a UTC hour unless the transformation is actually defined. To resolve a recurring ’09:00 at this site’ to concrete instants, supply the dates, the site zone and a rule for ambiguous or nonexistent local readings. A single fixed offset may be appropriate for a specified fixed-offset service; it is not a general substitute for a named zone’s date-dependent rules.
When comparing reader output, separate three observations: the saved annotation, the decoded number in its unit and the destination’s display. If only the display differs, inspect its formatter before blaming the file. If the decoded values differ, compare them with a hand-computed boundary fixture. If the interpretation is missing entirely, mark the compatibility gap instead of repairing it by guesswork.
CHAPTER 6 OF 9 · Cross midnight safely
6. Keep date predicates and timestamp predicates distinct
Suppose an archive has service_date and logged_at. The service_date is assigned by a business-day convention; logged_at records when a machine accepted the row. A request for service_date equal to 2028-02-29 need not be equivalent to ‘logged_at occurred during that calendar day’. A late upload can be logged on 1 March without changing the service day.
For an actual timestamp interval use two resolved boundaries, not an end-of-day guess such as 23:59:59. A half-open interval includes its first instant and excludes the next boundary. It works when stored precision changes from seconds to nanoseconds, and it keeps adjacent day windows from double counting their common boundary. If the report defines a local day, resolve both local boundaries under the report’s zone rule.
Use separate test rows: one at the opening boundary, one immediately before the closing boundary in the actual carrier’s unit, and one at the closing boundary. Then add a row with the service date set to the target day but an upload after that window. Decide expected row identifiers before looking at the query result. Equal totals are weaker evidence than equal membership because an extra row and a missing row can cancel.
DATE values order by signed day counts. TIME values of the same declared type order by signed numbers in the corresponding unit, which can support ordinary clock comparisons. Neither property licenses comparing a raw DATE integer against a timestamp literal without a correctly justified conversion. Page and row-group bounds can only discard candidates safely when their ordering and the query’s converted predicate refer to the same meaning.
Inspect the query plan if a reader claims to push a temporal predicate into storage. Then compare an independently decoded full-scan answer with the optimised answer. Turning off one visible option does not automatically establish that every optimisation is disabled. A simple trusted fixture read by known row identifiers is a practical starting point even when the production plan is complicated.
CHAPTER 7 OF 9 · Test a handover
7. Separate missing dates from business defaults
Zero is not a neutral stand-in for missingness. In DATE it is a real epoch date; in TIME it is midnight. If a form leaves return_date blank, and an importer substitutes zero, a downstream report may count a return in 1970. If opening_time is unknown, zero may incorrectly tell a customer the desk opens at midnight.
Distinguish absent, observed and imputed values. An unknown opening hour may have a null clock reading and a reason. A deliberately chosen default can be represented separately with its source. If the record says ‘not open today’, a missing time is only part of the story; the closed status matters too. Do not turn every missing reading into a number just because a plotting tool prefers complete rows.
A migration fixture should include an actual date of 1970-01-01, an unknown date, and a known value before the epoch. Use a real midnight, an absent clock and a value just before midnight. The test has to distinguish these after conversion. It should also reject a deliberate invalid clock rather than mistaking the rejection for successful missing-value handling.
For a table export, state the literal policy: for example, empty cells represent missing readings, while 00:00:00 represents a known clock value. Beware of conflating a blank input with an empty output caused by a renderer that suppresses zero. The consumer has to check the underlying value or an explicit missing marker, not just what looks empty on screen.
Reconcile with row identifiers and the original categories. A surprising increase in epoch dates may indicate a default rule, not a historic event surge. If a repair is justified, keep the original raw field or traceable source, document the affected rows and redo the downstream counts that used the wrong interpretation.
CHAPTER 8 OF 9 · Test a handover
8. Build a two-reader fixture before changing a schema
Start with a narrow matrix. For DATE include −1, 0, 1, a leap date and null. For TIME include zero, a nontrivial clock value, a fractional remainder when downcast, a near-midnight value and null. Add an intentional invalid record to a separate negative fixture so that an expected rejection cannot be confused with failure to read the normal file.
Write the expected record as several columns of evidence: row identifier; semantic claim; physical carrier; logical declaration and flag; expected exact number; expected human display. A date test might say D1, calendar day, INT32 DATE, −1, 1969-12-31. A clock test should distinguish time units explicitly. Use two consumers only if they actually exercise different implementations or conversion paths.
Check schema and decoded values first. Next check filters on the difficult boundaries, then check any conversion to a table, document or dashboard. If the output drops metadata, decide whether the receiving process needs it. A date printed unambiguously in an export can be adequate for a calendar list, whereas an unlabelled time number may be inadequate to reconstruct the source clock contract.
When a test fails, isolate one variable at a time. Change precision with the clock flag held constant. Then change the flag without changing the stored count. Compare old and new files across the same consumer, and both consumers on the same file. A checksum can establish that a file has not changed, but identical bytes do not guarantee that a new application interprets the annotation correctly.
Put the decision beside the result. ‘Clock milliseconds preserved, local flag lost in the text export’ is more useful than ’round trip OK’. A destination might be accepted for a printed timetable and rejected for a pipeline that later resolves dates and instants. The gold standard depends on what the receiving person or system needs to do.
CHAPTER 9 OF 9 · Test a handover
9. Transfer the method to reporting and measured intervals
Change the context from a library to a transport depot. A planned departure time is a recurring local clock label; an observed departure has an event timestamp; the reported duty date is assigned by an operational convention; journey duration is a measured interval. A duty date can deliberately differ from the civil date just after midnight. Do not overwrite one with the other simply to make a join easier.
A daily report uses a duty boundary at 03:00 local rather than midnight. An observed departure at 02:45 on the civil date 1 March can belong to the duty date 29 February in a leap year. That assignment depends on the stipulated convention and the observation’s properly interpreted local date and time. The rule cannot be reconstructed from the number 02:45 alone.
Try the transfer: the next depot uses a 04:00 boundary and reports an observed departure at 03:15 on 1 March. Under the new convention it still belongs to the previous duty date. Under the original convention it would belong to 1 March. The observed event does not change. What changes is the explicit business classification, so the report must retain the rule used to generate it.
Now add a duration of 26 hours for a delayed trip. Reducing it modulo 24 hours produces 02:00, which might look like a clock reading but loses the complete duration. A duration field should retain the 26 hours or an agreed exact unit such as 93,600 seconds. A date and a clock reading solve a different problem. This changed context tests whether the learner understands the distinctions without relying on the original column names.
In your own handover, choose one field and finish the sentence: ‘This value answers ___, but it cannot answer ___ without ___.’. Then design one boundary case that would catch an incorrect answer. That practical step is more durable than memorising the carrier of each type: it exposes lost meaning before a new report acts on it.
Independent worked case: reconcile a late equipment return
A fictional lab writes due_date = 2028-02-29, closes_at = 23:55 local, and returned_at as an instant. The return desk receives a kit at 00:04 local on 1 March. Assume the displayed dates and clocks were resolved with the lab’s actual zone, and there is no offset change across this nine-minute interval. A developer proposes using the returned timestamp’s date as the due date and subtracting the local clocks for the delay.
Separate the evidence. The due date is a calendar commitment, so it remains 29 February. The actual return’s local date is 1 March. The close on the due date is the stipulated deadline. From 23:55 to midnight is five minutes, and from midnight to 00:04 is four more. The delay is nine minutes under those facts. The raw subtraction of clock counts is −23 hours 51 minutes, not an elapsed measurement.
Change the policy: the same lab accepts returns until 00:05 the next date. The same observed return is now within the window by one minute. None of the saved observations needs to change. If policy version is absent, the original return record cannot by itself prove whether the deadline was met. Keep the deadline rule with the report.
For a verification handover, include the expected due date, derived deadline instant under each policy, observed return instant, decision under each policy, and a missing-clock control. Compare row identities and the duration; do not accept agreement on only the display date. If the clock or policy was not available, the correct output is unresolved rather than an invented nine-minute result.
Keep these distinctions visible
| Field meaning | Useful representation | Do not infer |
|---|---|---|
| Calendar day | DATE day count | A zone or midnight instant |
| Clock within a day | TIME with unit and flag | A particular occurrence or trip duration |
| Observed event | Appropriately resolved instant | The business date used for reporting |
| Elapsed interval | Explicit quantity and unit | A clock reading after taking modulo 24 hours |
Questions and practical checks
Does the Unix epoch make DATE a UTC timestamp?
No. The epoch date is the reference for counting calendar days. Choosing an instant for local or UTC midnight is a separate interpretation.
Can a TIME column store a 26-hour interval?
Do not use a time-of-day type for that measurement. Preserve a duration and its unit; a clock wrapped at 24 hours cannot distinguish two hours from twenty-six.
Why is 00:00 not the same as null?
Midnight is a known clock reading. Null represents an absent value under the schema, which may need a reason in the surrounding record.
Can two TIME files display the same value with different meanings?
Yes. Inspect the unit and UTC-adjustment flag rather than assuming identical printed labels establish the same semantics. The date needed for a unique occurrence is absent in both.
How do I catch a lossy precision change?
Choose an input with a nonzero remainder at the target unit and one just before midnight. Declare whether the conversion must reject loss, preserve the original or use an approved rounding rule.
What if my tool reports only a legacy time label?
Check a metadata reader that can show the modern annotation and compare boundary fixtures. Do not assume that the label alone proves the intended modern flag.
Continue by the question you need to answer
When you need an instant
Separate recorded events, time-zone resolution and business-effective history.
Parquet timestamps: UTC instants and local clock meaning
Event time, watermarks and late evidence
Historical state and as-of queries
When file filters change results
Inspect the chosen field and the assumptions that make a metadata-based skip safe.
Row-group statistics, predicate skipping and nulls
Page indexes, row ranges and selective reads
Field IDs, column renames and historical reads
Schema evolution and semantic drift
When a missing value changes the count
Keep real zeroes distinct from missing evidence.
Null, blank, zero and the meaning of missing
Sources and scope
The examples are teaching fixtures, not statements about a live dataset or any particular reader’s supported range. Parquet defines representations; the producer and receiving workflow still need an explicit interpretation and a boundary test.
Apache Parquet logical types — DATE, TIME, TIMESTAMP and legacy compatibility rules.
Apache Parquet metadata definitions — The modern logical type and legacy converted-type fields.
Apache Parquet page index — The boundary metadata used in page selection.
Python date and time objects — An example of a consumer distinguishing dates, times, datetimes and durations.
The scenarios and fixture values in this guide are original teaching examples, not reports about a live organisation. Format and implementation references were checked on 11 November 2026. The checking sequences are practical teaching suggestions; they do not certify a particular reader or production dataset.
Return to the Gold Standard of Education · Explore How X Works