Database types

Comdb2 supports 6 basic types:

1. INTEGER
2. REAL
3. TEXT
4. BLOB
5. DATETIME
6. INTERVAL

For purposes of storage and permissible values, these types have multiple subtypes. The following is a list of all supported types, their value ranges, and other related information. Note that in addition to a valid value of a given type, any column can hold an additional value of NULL.

Integer types

These types are basic fixed size integer. When queried via the C api, they are returned as a CDB2_INTEGER value, which corresponds to a C type of int64_t.

Floating point types

These are floating point types. They return CDB2_REAL values (double).

Decimal types

Decimal types are decimal representations for real numbers. They provide support for arithmetic on decimal numbers without loss of precision as long as the required number of digits can be accommodated by the format limitations of exponent and significant. See the decimal types section for more details. When queried, they return CDB2_CSTRING (string) values to avoid losing precision.

Blob types

Both BLOB types in Comdb2 are treated by the database engine as uninterpreted byte arrays. They are not convertible to any other type. BYTE fields must be declared with a size. When specifying field values, the value must match the size defined in the schema. Blobs are not declared with a size. Instead they can specify a size hint. Values of this size or smaller are stored inline with the rest of the record’s fields. Larger values are stored in a separate entity (and are thus slower to access). Another restriction on BLOB fields is that they may not be used as a part of any key. BYTE fields can be used in keys.

Text types

There are 2 text types. CSTRING is a fixed-size field. The size must be declared in the table schema definition. Values smaller than the declared size are allowed. Larger values are not. No validation is done on the string value, except that it may not contain 0x00 bytes.

VUTF8 is a variable length field. Like BLOB, the field can be optionally declared with a size hint. Values smaller than that size are stored inline in the record. Values larger (up to 255MB) are stored in a separate entity, with the associated access cost. VUTF8 fields may not be a part of a key. VUTF8 values must be valid UTF-8. Strings that contain invalid characters are rejected. VUTF8 values may not contain 0x00 bytes.

Both CSTRING and VUTF8 types are returned to applications as CDB2_CSTRING types. They both return the size of the value which includes the null-terminator. On input, the value gets truncated at the first 0x00 (if any) and anything beyond that gets discarded.

Datetime types

Comdb2 datetime types store the amount of time that has passed since the start of the time epoch (1970-01-01T00:00:00.000 GMT). On the way into the database, datetime values specify the local timezone. Time is converted from that timezone to UTC and stored. At query time, the application will pass its local timezone. Times are converted to this timezone before being sent to the application. Timezone information is not stored with the time value. Datetime can be stored with either millisecond or microsecond precision, depending on the type specified in the table schema.

The return type from the database for these types is a structure that contains fields for datetime components (day, month, etc.):

typedef struct cdb2_tm 
{
  int tm_sec;
  int tm_min;
  int tm_hour;
  int tm_mday;
  int tm_mon;
  int tm_year;
  int tm_wday;
  int tm_yday;
  int tm_isdst;
}
cdb2_tm_t;

typedef struct cdb2_client_datetime {
        cdb2_tm_t           tm;
        unsigned int        msec;
        char                tzname[CDB2_MAX_TZNAME];
} cdb2_client_datetime_t;

typedef struct cdb2_client_datetimeus {
        cdb2_tm_t           tm;
        unsigned int        usec;
        char                tzname[CDB2_MAX_TZNAME];
} cdb2_client_datetimeus_t;

Datetime fields are represented into a compacted form on disk. Applications use either a cdb2_client_datetime_t structure, a cdb2_client_datetimeus_t structure, or a string using a ISO 8601 modified format. An integer value converted to/from a datetime represents the number of seconds since the start of the epoch. A real value is seconds (integer portion) and milliseconds (fraction portion).

If the client specified a timezone, the datetime information is relative to this point local time. The client can specify a timezone inline with the value (eg: 2016-10-09T19:22:23 America/New_York), or set it per connection with SET TIMEZONE. In the absence of both, the client will send the value of the $COMDB2_TZ environment variable, then the $TZ environment variable. The final fallback value is America/New_York.

Learn more about datetime datatypes here.

Interval types

Interval datatypes represent the amount of time that passed between two absolute points in time. Subtracting datetime values results in an interval. There are 2 classes of intervals - years to months and days to seconds. This split is necessary since a “month” is a variable amount of time.

Learn more about interval datatypes here.