Timestamps: The Detail That Silently Breaks Analysis

A timestamp claims that one observation belongs to one moment and to no other, and it is the field most often stored as text rather than as a date. Sort the six year record of the Nakshatra unit by dates written day first as text, and 71 of its 72 observations land in the wrong place. The closing price stays at Rs 187.4539/-.

Every row of a record is built out of two things, and only one of them usually gets argued about. People check and question and quote the number at each other. People mostly just accept the moment the number is fastened to. The moment settles what all the numbers mean, and the moment is the field most likely to have been stored as plain characters rather than as a date. When a timestamp goes wrong it does not produce an error message. It produces an answer.

The Nakshatra unit, an invented traded object written for teaching, carries a six year record of 72 monthly observations, one for each month ending from 31 January 2019 through to 31 December 2024, measured out from an opening markA value fixed before the record begins, so that the first row has something to be counted from. Here it sits on 31 December 2018 and was picked rather than read off anything. of Rs 100.00/- dated 31 December 2018. CompoundingApplying each change to the value the previous change left behind, rather than to the starting value, so the changes multiply together instead of adding up. those 72 changes onto that mark one after another gives a price for each of the 72 months, and the last of them lands on Rs 187.4539/-, exact to the paisaThe smallest unit ordinary money is written in, a rupee split a hundred ways. Amounts matching that far down are matching about as finely as money ever gets recorded.. Nothing in that record was collected from anywhere. Every date on it is a label somebody wrote.

What does a timestamp on an observation actually claim?

Not that the observation is roughly from around then. A timestamp claims that this reading belongs to that moment and to no other one, and that claim is what makes every other calculation on the record possible. A row stripped of its number leaves a date pointing at nothing. A row stripped of its date leaves a number that could have come from any month of the six years. Such a number can be averaged and can be almost nothing else.

The same problem turns up in ordinary life wearing different clothes, and that version is the one worth carrying around. A snack stall works the pavement outside one office gate. The stall shuts at nine in the evening and the takings get written up the next morning over tea. Whether the slip written on Wednesday morning belongs to Tuesday or to Wednesday is a decision somebody made once, years ago, without thinking hard about it and certainly without writing the decision down. Every figure anybody ever works out of that book of slips inherits that decision, and nobody who reads the figures can see it.

Three different days are competing for one cell, and it helps to name them. One is the stretch the row measures, a whole calendar month on the six year record. Another is the first day the row could have existed at all, and no such day falls before the stretch has finished. The third is the day somebody wrote the row into a file, wherever the working week put it. The day a row is filed under, the day the thing it measures actually happened, and the earliest day it could have been worked out are three separate dates, and an ordinary file has room for exactly one of them.

The record is monthly and every label is a month end, so on the six year record the three sit close together. Pull a record together out of three different systems and they can be weeks apart, and the gap is never the same width twice. So do not ask what the date is. Ask what the date is of.

What goes wrong when the dates are sorted as text?

A great many systems write a date the way people say it out loud, day first: 31-01-2019, then 28-02-2019, then 31-03-2019. If that column has been read as a date, sorting it puts the months in order and nothing interesting happens. If it has been read as text, and text is what a column of characters is until somebody converts it, then sorting compares the two strings character by character from the left and stops at the first position where they differ.

The comparison is settled at position one, before the month has been looked at and long before the year has. In 28-02-2019 the first character is a 2 and in 31-01-2019 it is a 3, so the February row wins and goes first. Nothing about that is a bug. The sort did precisely what a sort of text does, on data that was text, and it will do it again tomorrow.

Run that over the whole of the six year record and the damage is close to total. The sort returns the record with 71 of its 72 observations in the wrong place. It opens at 28-02-2019, which is the February 2019 reading. Every 28th in the record arrives ahead of every 29th no matter which year it carries, so the next two rows are 28-02-2021 and then 28-02-2022. Then come the two 29ths, then the whole run of 30ths, then the 42 rows ending on a 31st. Exactly one row survives where it started, and it survives by luck rather than by anything meaningful.

The shape of that result is worth sitting with for a moment. The reordering is not random and it is not mild. The sort has grouped the record by day of the month. Day of the month is a property of the calendar and has nothing whatsoever to do with the Nakshatra unit. Six Aprils now sit consecutively. February 2019 is followed immediately by February 2021, two years later, and then by February 2022, and then the record jumps three years backwards to February 2020. Not one of the 71 steps between consecutive rows is a step of one month, and twelve of them run backwards in time.

Why does nothing on the screen give warning?

Because every routine figure a careful person prints when a file lands is a figure about the rows as a collection, not about the rows as a sequence. Sorting moves rows and never adds or removes one, so the count is 72 before the sort and 72 after it. Addition gives the same total whichever end it starts from, so the average of the 72 monthly changes comes to 1.00 per cent either way. The spreadA single number for how far the observations sit from their own average, got by squaring every distance, averaging those squares and taking the square root. The full construction is set out elsewhere. is built out of that same average and the same set of distances, and the order they are listed in makes no difference. It comes to 5.00 per cent either way as well.

The closing price is the one that ought to feel impossible, and it is the one that makes this fault so hard to catch. Compound the scrambled record forward from Rs 100.00/- and it closes on Rs 187.4539/-, matching the date ordered record down to the last paisa. There is nothing approximate about that and nothing remarkable about it either. The same 72 factors are multiplied together, and multiplication does not care about arrangement. Rs 100.00/- multiplied by 1.05 then by 0.98 lands exactly where Rs 100.00/- multiplied by 0.98 then by 1.05 lands.

So picture the screen. Seventy two rows. An average of 1.00 per cent. A spread of 5.00 per cent. A closing price that agrees to four decimal places with the true one. Everything reconciles. A record whose order has been destroyed passes every check that a heap of numbers can pass, and a file that reconciles is a file nobody looks at twice.

Which figure notices, and why is it the only one that could?

The record does give one tell, and only one that anybody actually prints. The autocorrelationHow much one observation looks like the one before it, on a scale where zero is no resemblance at all. Built in full on the notes covering how a series is lined up against its own past, and used here purely as a figure that reads along the order. at one month reads 0.2011 on the record in date order. On the text sorted version the same 72 observations read 0.0378. The autocorrelation fell to under a fifth of its own size, a fall of 81.2155 per cent, and it is the only one of the printed figures that reacted at all.

The reason is the whole working lesson, and it is worth stating plainly. The autocorrelation is computed by pairing each observation with the one immediately before it. Pairing is what makes it the only figure on the list that asks a question about the arrangement. Everything else asks a question about the contents. A fault in the order can only be seen by a calculation that reads along the order, and no amount of care spent on the other kind will substitute for it.

Two other quantities on the record also read along the order, and both of them moved as well, but they moved so gently that they teach a second lesson rather than the same one. The lowest point the path ever touches reads Rs 96.8923/- in date order and Rs 91.0309/- after the sort, a shift of about six per cent of its own size. The count of months whose price sits under the opening mark reads 5 and then 8. Both are genuinely order dependent and both would be waved through by anybody who did not already know what the answer should be. So being order dependent is what lets a check see the fault, and moving loudly enough to be believed is a separate requirement on top of it.

And there is a harder consequence hiding behind that. A reader who has never computed an autocorrelation on a file has no way at all to catch this. Not a hard way, not an expensive way: no way. Every instrument they own is pointed at the contents of the heap.

What does one date column look like under both orderings?

One date column, two arrangements, the same 72 observations throughout. The first four rows describe the dates themselves and the rest describe what anybody would compute from the file.

Read the bottom half of that table on its own, which is what a person looking at a summary actually sees. Four of the five headline figures are word for word identical. The only two columns that disagree loudly are the ones nobody prints and the one figure at the bottom, and if the file arrives with its date column already dropped, even the top four rows are gone.

Why is a month not a fixed length of time?

Now for a second fault in the same field, and this one is not anybody breaking anything. The calendar is simply what it is. The 72 month end dates in the six year record carry four different month lengths: 28 days, 29, 30 and 31. 2019 to 2024 contains two leap years rather than one, so forty two of the months run to 31 days, 24 run to 30, four are 28 day Februaries and two are 29 day Februaries. The four lengths come to 2,192 days across the record, an average of 30.4444 days a month, and not a single one of the 72 months is actually that length.

A monthly figure compared against another monthly figure is comparing like with like, so most of the time the difference in length costs nothing. The difference starts costing the moment anybody converts. A change of 1.00 per cent across January works out at 0.032103 per cent a day, and the same 1.00 per cent across February 2019 works out at 0.035543 per cent a day, a ratio of 1.1072. Both statements describe a month that moved by exactly one per cent. The two statements disagree about the daily speed by almost eleven per cent, and the only thing that differs is how many days the month had.

The other direction is just as unforgiving. The daily figure that a 30 day month implies, 0.033173 per cent, applied across 31 days instead, gives 1.033505 per cent rather than 1.00 per cent. Every conversion between a monthly figure and a daily one has had to pick a month length, and almost no file records which length it picked. A household version makes the same point without any arithmetic: rent is the same amount in February as in March, so the daily cost of a roof is higher in February, and nobody thinks about that because nobody ever divides.

What does a month end label claim, and what does one month of slip cost?

Every observation in the six year record is labelled with the last day of the month it describes. The January 2019 reading is dated 31-01-2019 and the December 2024 reading is dated 31-12-2024. The last day of the stretch is the first moment the reading could exist, so labelling by month end is a sensible convention. Month end is not the only sensible convention. Plenty of systems label the same reading with the first day of the following month, on the reasoning that the reading became available then.

Relabel the whole record that way and watch what happens to it. The January 2019 reading becomes a February 2019 reading. Every observation moves one month forward. The December 2024 reading lands in January 2025, outside the six years the record covers, and January 2019 is left holding nothing. Six of the 72 rows change calendar year, because six of them are Decembers.

The obvious conclusion is the wrong one. Neither convention is a mistake, and a record built entirely on either one is perfectly usable. Mixing them is the fault, and mixing them is what happens when a record is assembled out of more than one place.

Put numbers on it. Suppose the first three years of the record come from a system that labels month end and the last three come from one that labels the first of the following month, and somebody stacks them into one file and parses the dates. Nothing is lost and nothing is duplicated: there are still 72 rows carrying the same 72 values in the same order. But those 72 rows now spread across 73 month slots, 36 of them are dated one month later than the month they describe, and the slot for January 2022 holds nothing at all.

The cost of that shows up the moment anybody asks a question that depends on which calendar month a reading belongs to. Work out what each calendar month averages across the merged file and eleven of the twelve answers move. February, for instance, reads 3.00 per cent on the record as written and 4.00 per cent on the merged one, an entire percentage point out, and December is the only month that happens to land back on its own figure. Not one row was wrong on its own, and eleven of twelve answers came out wrong together. How a repeating calendar pattern is measured properly is covered separately; what matters here is that the damage was done by the labels and by nothing else.

How is a date column checked before it is trusted?

Five checks, all cheap, and what matters about each one is what it is capable of seeing. The obvious ordering of the five turns out to be the wrong one.

One. Parse every row and confirm the parse succeeded on all of them, not on most of them. A column where seventy of seventy two convert cleanly is not a column that is nearly fine; it is a column with two rows written in a different style, and those two are usually the interesting ones. Note carefully what this check cannot do: it looks at cells one at a time, so it passes identically on the record in date order and on the scrambled one.

Two. The first and last dates, printed and read directly. On the six year record ought to hand back 31 January 2019 at the top and 31 December 2024 at the bottom. Under the text sort the first row reads 28 February 2019. A wrong first row is a real tell, but only for somebody who already knew what the first row was supposed to say.

Three. Count the rows and compare that against the span the dates cover. Seventy two monthly rows have to span 71 months from first to last. The text sorted record runs from 28 February 2019 to 31 December 2024, a span of 70 months, and 72 monthly rows cannot fit into 70 months. A row count set against a span needs no prior knowledge of the record at all, and it catches the fault outright.

Four. Confirm the step from each row to the next is the same step all the way down. Here it is one calendar month, 71 times. Under the text sort not one of the 71 steps is a month: the first jumps 24 months, the fourth runs 36 months backwards, and 12 of the 71 go backwards in time. The step check is the loudest of the four and the one that fails on the very first pair it looks at.

Five. Compute one figure that reads along the order, before any sort and again after it, and confirm it did not move. On this record the one month autocorrelation goes from 0.2011 to 0.0378, which is not a subtle difference. The autocorrelation check is the only one of the five that reads the values rather than the dates.

So which of the five would actually have caught it? Three of them, more than most would expect. When a check runs matters more than which check it is. Checks two, three and four all read the date column, so they only work if they are run after the last thing that could have reordered the rows, and the ordinary habit is to check a file when it lands and tidy it afterwards. A check run before the fault existed has not checked anything. A second, quieter way to lose all three is to keep only the value column for the calculation. People do that constantly, and then checks two, three and four have nothing left to read. Check five is the one that still works then, and it is the only one that does.

The same shape turns up in ordinary life, and not as an abstract worry. A bank passbook carries two dates against many entries, the day the entry was posted and the day it counts from, and they are not always the same day. An analyst stitching a record together out of two systems is doing exactly what the merged file above did. A lender reading a borrower month by month is asking a question that only makes sense if every row is filed under the month it describes, and a lender who is out by one month is reading last month against the month before it and calling it this month. In every one of those the numbers are fine and the labels are the whole story.