• user warning: Unknown column 'u.signature_format' in 'field list' query: SELECT c.cid as cid,, c.nid, c.subject, c.comment, c.format, c.timestamp,, c.mail, c.homepage, u.uid, AS registered_name, u.signature, u.signature_format, u.picture,, c.thread, c.status FROM comments c INNER JOIN users u ON c.uid = u.uid WHERE c.nid = 19514 AND c.status = 0 ORDER BY c.cid LIMIT 0, 50 in /var/www/ on line 991.
  • warning: file_get_contents( [function.file-get-contents]: failed to open stream: HTTP request failed! in /var/www/ : eval()'d code on line 4.

Timestamp, Mon Amour

by Olivier Deschanels

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…


But first and foremost, what’s a timestamp? Don’t look for it in the 4D documentation – this concept doesn’t exist as such in our beloved product. A little jaunt through Wikipedia tells us that a timestamp is a “sequence of characters, denoting the date and/or time at which a certain event occurred.” A-ha! So it’s a combination of various information and, indeed, in our matter of the day, we’ll be combining date and time.

Etymologically speaking, timestamp brings to mind the rubber stamp used for postmarks, and you’ll see that effectively timestamps give us the simple joy portrayed in Jacques Tati’s The Big Day. This little rubber stamp materialized – and still materializes – the date and time the letter was sent, most often accompanied by the post office of origin. Don’t think at all that this old concept is obsolete. Remember, the term “must be postmarked by…” still exists. Just try sending in your taxes after midnight on the due date and you'll see that postmarks still apply.
So the order of the day is the timestamp. The idea is to be able to mark records with the time and date of the last modification, for example. Ok, there isn’t anything complicated about leaving this mark, you say. In fact, it suffices to fill two fields in the record – date and time – like the good old seal placed on top of the postage stamp.

This solution has been used by many developers and has the advantage of being extremely simple to implement. Generally, the two fields are filled when triggered by saving the table.

In the presence of such simplicity, why look any further and use a timestamp? Before giving my argument – because I most certainly have one – I first want to make a point about these two fields.
First of all, let’s examine the footprint left by these two fields in the data file. A date field weighs 8 bytes in a 4D v11 SQL data file (only 6 in previous versions). A time field also weighs in at 8 bytes (4 in previous versions). For that, we must add 8 bytes per field to the title of the micro-structure (the object present in each record and necessary for 4D to manage data; 4 bytes in previous versions). The total weight is thus 8+8+8+8 = 32 bytes (6+4+4+4 = 18 bytes in previous versions).
NOTE: The date and time fields weigh a few bytes more in version 11 in order to be compatible with SQL standards.

Now let’s look at the processing side.


If your two-field stamp will serve to organize, for example, a waiting list of patients (first arrivals, first to be seen) then you will have to sort on two fields such as:

ORDER BY([Visit];[Visit]Arrival_Date;>;[Visit]Arrival_Time;>)
Let’s suppose that this waiting list carries a number of important records. In this case you’ll be tempted to index one of the fields, look at them both, to accelerate the process. However, this index is of consequence, because a sort on two fields is always sequential if you remember the 4D documentation. Of course, users of version 11 can add a composite index. That would cost a minimum of 70 bytes per key, which is to say per record because it’s of the type B-Tree and not Cluster.
At present, we should perform a search on the visits for the week between 8:30 Monday morning and 5:00 pm Friday. The search is rather simple. It’s a search split in two pairs of fields:
If we take the case where one has to shift the workday to 5:00am for a production line running all day/night, the code isn’t as simple to write because it doesn’t suffice to add a shift to the time field. In effect, we must also check that the resulting value doesn’t go past midnight, because the day will have to change. The code should thus look like this:
End while

Finally, I’ll let you imagine the code you have to write to calculate the time difference between two shifts that didn’t start on the same day nor the same hour. You have to start by looking at which started first, then calculate the time of day and finally adjust the calculation and count up the hours.


Only while thinking of this last example, I said to myself that it’s time to give an explanation of the timestamp… But like all good TV broadcasts we’ve come to the commercial break. See you back soon for the rest!



RSS 0 comment(s) to this post