This isn't the latest slogan from a movement of angry accountants, but a very serious proposition that I'd like to cover today.

In our profession as application developers, we have ever-growing masses of data to store and to manipulate. More and more often we have to produce results based on this mass of data, and understandably our users don't want to wait too long for a response while they query the database, looking for a specific result, a list, or a dashboard.

This blog post is the third in a four-part series written in French in September 2009.

In previous posts we’ve seen the use of a timestamp based on a long integer, which brings us much in terms of efficiency, saving space and performance. Let’s now look at how to code the conversion of a date and time into a timestamp and vice-versa.

In the end, the timestamp is calculated by the number of seconds compared to a reference date. To keep it simple, we’ll use January 1st, 2000 as our reference date. This allows us to go up until 2068. Why 2068 and not 2136, assuming that 136 years corresponds to 4 billion seconds? Because long integers in 4D are signed, we must remember that the value 0 is in the middle of the range of possible values. As a result, our timestamps allow us to navigate between the years of 1932 and 2068. Of course, there’s nothing preventing you from shifting your reference date to cover your needs better.

The principle of the timestamp is to combine in a single field, in the most compact way possible, the information needed to memorize the date and time. Of course, the less space we attribute for storage, the smaller the amplitude of possible values.

This blog post is the first of a four-part series written in French in September 2009.

Far be it from me to rip off Marguerite Duras (writer of Hiroshima, My Love) with such a title, just that I want to express how much timestamps have simplified my life and how much I love to use them. I could even say that timestamps are the bomb, pun somewhat intended…