• 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 = 19516 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 (Part 3)

by Olivier Deschanels

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.


Here’s the code I’m proposing to encode a timestamp:

`method : TimeStamp_Write

   ¬ $minute_in_seconds;$days_in_seconds)

Case of
    : (Count parameters=0)
        $date_ref:=Current date
        $time_ref:=Current time
    : (Count parameters=1)
        $time_ref:=Current time
    : (Count parameters=2)
End case

$date_reference:=Add to date(!00/00/00!;2000;1;1)



The method I’m calling Timestamp_Write accepts a date and time, and it returns the corresponding timestamp. Note that the parameters are optional.


4D only allows one value to be returned via one function (except to pass by pointers), so I’ll propose two methods to find the date and time from a timestamp:

`method: TimeStamp_Read_Date

$date_reference:=Add to date(!00/00/00!;2000;1;1)
$nbdays:=$1\ 86400



`method: TimeStamp_Read_Time


   ¬ +":"+String($seconde;"00"))


The decoding is based on whole divisions and modulos, which are operations loved by processors – and thus very rapid. Also note that the use of the Add to date function to construct the reference date. The choice of this means of writing assures the compatibility of the code, whatever the system adjustments may be to format the date.


The methods above being extremely simple, I’d never hesitate to use a timestamp. I really love this way of coding the instants that I need to keep track of in my databases. As a final thought, I have the satisfaction of using nothing but long integers which are, like all simple types, favored by the 4D engine, the system, and the processor. My queries are more simple, my sorts can be indexed, and calculations of shifts are basic…

RSS 0 comment(s) to this post