SQL Operations Guide

Previous Topic

Next Topic

An example field conversion

Consider a field called “MYTIME”. It is declared in the DODA as a CT_INT4 field. Just by inspecting the table definition data, there is not enough information to decide if a CT_INT4 field is in fact an integer field or a MYTIME field.

The MYTIME information stored in a CT_INT4 field is based on the C Runtime Library time (RTL) routines. The C RTL time value is a number that represent the number of seconds since a given date in the past. The value implementation is system dependent and the C RTL time handling functions should be used to guarantee portability.

The MYTIME’s CT_INT4 fields are transparently converted to CT_TIMESTAMP fields by the callback code. This transparent conversion allows FairCom DB SQL to properly display the time stamp data and the FairCom DB SQL parser will allow the use of SQL date, time and time stamp functions to be applied to the field data. Without the transparent conversion performed by the callback code, the MYTIME fields would be handled by FairCom DB SQL as integer fields.

The MYTIME data is converted to CT_TIMESTAMP format using the following pseudo code.

Example Conversion

time_t tmpval;

struct tm* ptr;

/* copy the MYTIME value to a temporary variable */

memcpy(&tmpval, &src_recbuf[srcofs], srclen);

/* adjust local timezone to UTC timezone */

tmpval -= 21600;

/* convert a time_t value into a broken-down time, expressed in UTC */

if ((ptr = gmtime(&tmpval)) != NULL)



/* convert the broken-down time_t values to c-treeDB API time stamp */

if ((Retval = ctdbDateTimePack(&stamp, (ptr->tm_year + 1900), (ptr->tm_mon + 1), ptr->tm_mday, ptr->tm_hour, ptr->tm_min, ptr->tm_sec)) != CTDBRET_OK)

goto Exit;

/* write the converted time stamp data to the destination record */

os_memcpy(&dst_recbuf[dstofs], &stamp, sizeof(stamp));





goto Exit;


In the example above, the src_recbuf is a source record buffer read from an ISAM data file with a MYTIME field. srcofs is the offset in the record buffer of the MYTIME field. dst_recbuf is a translated record buffer that will be presented to FairCom DB SQL with the MYTIME’s CT_INT4 fields translated to CT_TIMESTAMP field type.

The CT_TIMESTAMP data is converted back to MYTIME format using the following pseudo code.

Example Reverse Conversion


NINT year, month, day, hour, min, sec;

time_t tmpval = 0;

struct tm ptr;

/* copy the time stamp field to temporary variable */

os_memcpy(&stamp, &src_recbuf[srcofs], sizeof(stamp));

/* clear the C RTL time structure */

os_memset(&ptr, 0, sizeof(ptr));

if (stamp != 0.0)


/* break down c-treeDB API time stamp data */

if ((Retval = ctdbDateTimeUnpack(stamp, &year, &month, &day, &hour, &min, &sec)) != CTDBRET_OK)

goto Exit;

/* set the C RTL time struct */

ptr.tm_year = year - 1900;

ptr.tm_mon = month - 1;

ptr.tm_mday = day;

ptr.tm_hour = hour;

ptr.tm_min = min;

ptr.tm_sec = sec;

/* build a new time_t value */

tmpval = mktime(&ptr);

/* convert from UTC time to local timezone */

#ifdef ctPortWIN32

tmpval = tmpval - _timezone + 21600;


tmpval = tmpval - timezone + 21600;



/* copy XTIME data to record buffer */

os_memcpy(&dst_recbuf[dstofs], &tmpval, sizeof(tmpval));

In the example above, the src_recbuf is a source record buffer passed by FairCom DB SQL with CT_TIMESTAMP fields. srcofs is the offset in the source record buffer of the CT_TIMESTAMP field. dst_recbuf is a translated record buffer that will be written to an ISAM table with the appropriate CT_TIMESTAMP fields translated to CT_INT4 fields. dstofs is the offset into the destination record buffer of the MYTIME field.