Managing Date/Time Fields in C

As explained in the Datetime FIelds page, a field of datetime type is a 64 bit integer value used to store the date and time in databases and the precision of datetime values is determined by the runtime parameter DATETIME_PRECISION which represents the number of ticks per second. The default value for DATETIME_PRECISION is 1, which means that the database stores a datetime value as the number of seconds since the EPOCH (i.e. 00:00:00 UTC 1 January 1970).

For example , the following schema snippet defines date/time fields and sequences of date/time values:

 
    class T 
    {
        date d;
        time t;
        datetime dt;
 
        sequence<date> sd;
        sequence<time> st;
        sequence<datetime> sdt;
    }
     

The resolution of datetime fields can be changed by an application via the mco_runtime_setoption() API for option MCO_RT_OPTION_DATETIME_PRECISION. The allowable precision (resolution) values range from 1 (for the number of seconds) to 1000000000 (for nanoseconds). For example the following code snippet sets the precision to 1000000 (1,000,000) to store the number of microseconds since the EPOCH:

 
    mco_runtime_setoption(MCO_RT_OPTION_DATETIME_PRECISION, 1000000);
     

The two functions mco_time2ticks() and mco_ticks2time() can be used to convert datetime between the application's and database's resolution. For example, consider an application storing time values in milliseconds (i.e. : MCO_RT_OPTION_DATETIME_PRECISION == 1000) in variable app_time. The following would store the number of milliseconds since the EPOCH in the field T.dt:

 
    T_dt_put(&obj, mco_ticks2time(app_time, 1000));
     

Similarly, the following sample stores the current time, returned by mco_system_get_current_time():

 
    T_dt_put(&obj, mco_ticks2time(mco_system_get_current_time(), 1000*1000))
     

Here the second argument in the call to mco_ticks2time() is 1000*1000 because mco_system_get_current_time() returns time in microseconds.

Similarly, the mco_time2ticks() is used to convert a datetime value loaded from the database to the desired resolution. For example:

 
    mco_datetime dt;
    T_dt_get(&obj, &dt);
    printf("dt (in msecs) = %llu, dt (in usecs) = %llu\n", 
        mco_time2ticks(dt, 1000), 
        mco_time2ticks(dt, 1000*1000));
         

Mapping SQL Date and Time fields

In order to map C-language date or time data to the SQL data types, a C application should use an 8-byte mco_datetime data type (instead of the mco_time or mco_date). The date and time types are represented via the 8-byte tpDatetime type in SQL. (Use the %l or %*l substitution specifier as this is an 8-byte or int64_t value.)