Skip to content

Personal tools
You are here: Home » Of Interest » Articles of Interest » Killing Time
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3558

Killing Time

by Joe Celko

How long is a minute? If you said 60 seconds, you are technically wrong. It can vary from 59 to 61 seconds because of the leap second adjustment. This is the little adjustment that keeps the solar time aligned with the time calculated by an atomic clock. The Earth wobbles a little bit and it is not a precise as the atomic clock.

I am probably one of the few people who sets his wristwatch to the leap second. But a lot of networks, geopositioning satellites and other communications systems really have to worry about it.

Timing is Everything

The United States Naval Observatory sent out a questionnaire concerning the effects of a redefinition of Universal Coordinated Time (UTC) and runs a chat group at on the subject.

On 2000 July 2, they issued an "Abstract and Conclusions" on their e-mail survey to find possible adverse effects of a redefinition of UTC. They identified some possibly expensive or unsolvable problems with rewriting or checking software, which I will get to in a minute.

The big problem was the cost of redoing satellite systems software. UTC is commonly confused with the old Greenwich Mean Time and is computed by occasionally adding leap seconds to International Atomic Time (TAI). Since 1972, leap seconds have been added on December 31 or June 30, at the rate of about one every 18 months to keep atomic time in step with the Earth's rotation.

I would recommend that you use only TAI or UTC, since a man with two watches is never sure what time it really is.

But many major navigation systems such as GPS use constant offset from TAI internally. For example, GPs is 19 seconds off of TAI. There is a proposal in the international timing community to redefine UTC to avoid the discontinuities due to leap seconds. A discussion of the reasons for a change and what they might be has been published by McCarthy and Klepczynski in the "Innovations" section of the November 1999 issue of GPs World (you can get an abstract of the McCarthy and Klepczynski paper at

The major reason they give for wanting to change the current system is to keep spread-spectrum communication systems and satellite navigation systems compatible with each other and with civil times. Another reason is the emerging need in the financial community to keep all computer time-stamps synchronized, which is where us database people need to start worrying about what we are doing on the Internet and communications networks.

If you do not add new leap seconds, solar time and atomic time will diverge at the rate of about 2 seconds every 3 years, and after about a century the difference would exceed 1 minute. Think of it as a Y2K problem on a smaller scale. Most commercial software assumes that UT1 is the same as UTC, or that the difference is always less than some value. If the difference is greater than that value, the software will have overflow problems. This would happen in NIST's WWV, WWVH and WWWB transmissions, which do not allow enough space for the difference to exceed 0.9 sec.

Specifying "Lawful Time"

Another problem is that some countries specify "lawful time" in terms of solar time, or GMT (Greenwich Mean Time, which has not existed for thirty years). Most nations on the Earth have learned to live with daylight savings time and moved from GMT to UTC. If you would like a history of the legal issues raised by past changes in time definition, get a copy of the book Greenwich Time and Longitude by Derek Howse.

Along the same lines, we survived Y2K, but nobody talks about what we learned from it. For a lot of companies, this was the first time anyone had looked at their legacy systems in years -- in decades, in fact. I think we can assume that any legacy system that was easy and cheap to replace was replaced. The next class of systems were those that we thought would be easy to patch, and on those systems, the Y2K staff went to work.

There was also a third class of software about which nobody knew anything, but that existed, nonetheless.

The side benefit of inspecting this class of programs was that while the programmers were fixing the date handling code, they could also fix any other bad code they found. I do not know if anyone collected statistics on how much the non-temporal parts of the legacy systems were rewritten as part of the Y2K efforts.

Avoid Headaches with Preventive Maintenance

I would like to suggest that it would be a good idea to set up regular maintenance policies on legacy systems. After all, you schedule regular maintenance for your automobile. Vendors release new versions of your packaged software. But most companies use the, "If it's not broken, don't fix it!" policy instead.

I appreciate the fact that programmers have to develop new software, and have to try to keep the existing systems up and running by making repairs to the code that's known to be broken.

But how much trouble would be avoided if someone went to the database, looked at trends, and increased or changed things before they broke?

Preventive maintenance could be done to the to the database as well as to the source code. For example, imagine that every month the average length of a VARCHAR(n) column in a table is getting longer. Why not make the column's upper bound greater with an ALTER TABLE now to avoid future problems? On the other hand, could performance be improved by altering a column to a smaller sized datatype, say INTEGER to SMALLINT?


Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 450 magazine columns and four books, the best known of which is SQL for Smarties (Morgan-Kaufmann Publishers, 1999). He is the Vice President of RDBMS at North Face Learning in Salt Lake City.

Contributors : Joe Celko
Last modified 2005-04-19 02:02 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone