Dates and Times in SQL Server: the science of time

Now that we have covered the various date and time data types (see the post from last time) in SQL Server and Azure SQL Database, it’s time to review what we know about time itself and how it is measured, so that we have a basic understanding of how and when to use these data types and associated functions. If any of you are students of Albert Einstein and his theory of relativity, this might seem familiar.

The speed of light (in a vacuum)

Cars travel on highways at around 120 km/h (kilometers per hour), or 75 mph (miles per hour). This is something most of us can relate to fairly easily. If we increase our speed, we get where we’re going slightly faster, but all cars have a speed limit dictated by the manufacturer and / or the laws of physics.

Light (and electricity) is part of the electromagnetic spectrum, so electromagnetic waves move at almost the same speed in a vacuum, which is approximately 300,000 kilometers per second, or 186,000 miles per second.

As Wikipedia states, the “speed at which energy or signals travel down a cable is actually the speed of the electromagnetic wave in the insulating material between the conductors, not the movement of electrons”.

If we convert that to a speed relative to highway travel, a photon (a particle of light) can travel 18 million kilometers, or just under 12 million miles, in one hour. That’s 150,000 times faster than our average highway speeds. It’s why police radar and lasers can track our speed before we even notice they’re there (see more about reaction time below).

This sounds fast, but when we start moving electricity or light through copper wire and optical fibre respectively, they slow down a lot. The electromagnetic wave will be as much as 50% slower, because copper and fibre optics have density and resistance (and routers in the way), meaning that electricity, and thus data, moves slower through them.

[A comment below contains additional information about how electrons move.]

By way of comparison, consider how hard it is for us to run through water in a swimming pool. That’s resistance.

A bit of guesswork

The Earth is an oblate spheroid, which means that it is a sphere that is flattened slightly on the top and bottom due to gravity and its spin around the axis (centrifugal force). Despite this flattened shape, when we calculate how light and electrons move over the surface through various transport mechanisms (including the air), we make calculations slightly easier using simple geometry, at a cost of accuracy. So for the rest of this post, we assume the earth is a perfect sphere.

The main reasons for this assumption have to do with scale and routing. The speed of light is so fast that it doesn’t really matter if we get the exact size of the earth correct for most measurements of time, plus we cannot always control which path the data will take through a network.

This means that, for electricity in the form of data moving bits of information through the Internet—through routers, firewalls, copper wire, optical fibre, and so on—we can work out, based on the planet’s average circumference, how fast it should travel.

Under perfect conditions, assuming a 33% slowdown from its maximum speed based on the transport medium, with an average planet circumference of approximately 40,000 km (25,000 miles), an electromagnetic wave will get a packet of data to do a complete trip around the Earth’s equator in around 200 milliseconds.

Thus, when testing network latency, we use a so-called “ping test” to send a packet somewhere using the ping command, and the time it takes to do a round trip (there and back again) is called the total latency. In this example, due to the speed of light and prevailing network conditions, 200ms is considered a really good latency for network packets travelling to the other side of the planet and back again.

Human reaction time

Humans have slow reflexes, relatively speaking. The fastest Olympic athletes take just under 170 milliseconds (thousandths of a second) to react, while the average human has a reaction time of more than 250 milliseconds. (Incidentally, if we want to try dodging a bullet, it takes an additional 250 milliseconds for our brain to get our bodies to move out of the way. By that time, we’re already dead.)

So if we want to know what time it is by looking at our watch, once we lift our wrist, turn our heads to look at the watch, have the image projected (at the speed of light) to our retinas, and our brains have interpreted and reacted to that information, a good half a second has already gone by. The data we rely on as humans is at least 250 to 500 milliseconds old by the time we know what to do with that information.

Computer reaction time

Computers are faster, of course. Much faster, because modern CPUs have as many as four billion clock cycles per second (measured in hertz, where four billion is written as 4 GHz). Although a CPU spends a lot of its cycles waiting for things to happen, it is capable of processing billions of instructions per second. Meanwhile, our conscious thought is struggling along with a measly four instructions per second.

Summary

However, there’s a slight snag in how computers keep track of time, and it has to do with the electrical grid, gravity, time dilation, network latency, and DNS.

Tune in next week for a look at this and more. In the meantime, you can find me on Twitter at @bornsql.