FairCom DB API for C

Time Types

CTTIME is declared as a 32-bit unsigned integer, stored as the number of seconds since midnight. The CTTIME type was implemented to be compatible with FairCom's r-tree and ODBC products.

FairCom DB API provides functions and methods to allow the user to manipulate the CTTIME type in their applications.

Function	Operation
ctdbTimeCheck	Check an unpacked time in hour, minute and second and return CTDBRET_OK if the time is valid. Hour must be a value between 0 and 23, minute and second must be a value between 0 and 59.
ctdbTimePack	Pack a time in the form hour, minute and second into a CTTIME form.
ctdbTimeUnpack	Unpack a CTTIME time into hour, minute and second.
ctdbTimeToString	Convert a packed time value into string format.
ctdbTimeMsecToString	Convert a packed CTTIMEMS into a string.
ctdbStringToTime	Convert a time in string form into a packed time. TimeType() gives the string form of the time value.
ctdbGetHour	Retrieve the hour component of a packed time. The hour return is a 24-hour value (e.g. 11 pm is returned as 23).
ctdbGetMinute	Retrieve the minute component of a packed time.
ctdbGetSecond	Retrieve the second component of a packed time.
ctdbGetDefTimeType	Retrieve the default time format kept by the session. The record manager uses the default time format to convert time fields into string fields and vice-versa.
ctdbSetDefTimeType	Set the default time format for the session. The record manager uses the default time format to convert packed time fields into string values, and vice-versa. When a session handle is allocated, the default time format is set to CTTIME_HMP (HH:MM am/pm)
ctdbCurrentTime	Retrieve the current system time.

Time Type Formats

Time Types can be one of the following string time formats:

FairCom DB API Symbolic Constant	FairCom DB API .NET Symbolic Constant	Description
CTTIME_HMSP	HMSP_TIME	Time is hh:mm:ss am\|pm
CTTIME_HMP	HMP_TIME	Time is hh:mm am\|pm
CTTIME_HMS	HMS_TIME	Time is hh:mm:ss (24 hour)
CTTIME_HM	HM_TIME	Time is hh:mm (24 hour)
CTTIME_MIL	MIL_TIME	Time is hhmm (military)
CTTIME_HHMST		Time is hh:mm:ss.ttt (24 hour)

Millisecond Resolution

In V11 and later, a new base c-tree type, CTTIMEMS, was added to hold a time with millisecond format. CTTIME and CTTIMEMS are not comparable without transformation. CTTIME is a LONG value holding the number of seconds since midnight, CTTIMEMS is a LONG value holding the number of milliseconds since midnight.

New FairCom DB API functions were added to set and get fields as CTTIMEMS, specifically ctdbGetFieldAsTimeMsec and ctdbSetFieldAsTimeMsec.
Calling ctdbSetFieldAsTimeMsec against a CT_TIME field would cause a loss of milliseconds. If this attempted, the error CTDBRET_MSEC_NOTSUPPORTED (4147) is returned.
New functions were added to FairCom DB API time with millisecond manipulation functions: ctdbTimePackMsec and ctdbTimeUnpackMsec.

Searching an index based on CT_TIME is problematic when milliseconds are significant since the index information does not contain milliseconds and, as we do with filter, we cannot automatically add milliseconds before doing the search. SQL will error out when performing index searches on CT_TIME columns.

Note: Millisecond support requires new extended data types in FairCom DB V11 forward.

Several functions were added to FairCom DB API related to TIMESTAMP (CTDATETIME):

CTDATETIME <-> CTTIMEMS conversions:

ctdbDateTimeSetTimeMsec()

ctdbDateTimeGetTimeMsec()

Millisecond DateTime handling:

ctdbDateTimePackMsec()

ctdbDateTimeUnpackMsec()

Timestamps with milliseconds are supported on existing timestamp data with no conversion. A minor behavior change should be noted:

Prior to this release, a query similar to the example shown below would have ignored the milliseconds portion, returning records matching '11/11/2015 12:00:15.000':

SELECT * FROM mytable WHERE mytimestamp = '11/11/2015 12:00:15.998'

With the changes described above, the milliseconds are now significant.