Edit this page on github
Frequently asked questions on comdb2


Datetime overview

Datetime is a comdb2 datatype that represents an absolute position in time, or timestamp (for example, 2008-02-01T10:01:00 GMT).

Timestamps are preferred to packing dates into integers (eg: storing time() output, or packed date values like 20080201)

There are many advantages by doing this, like:

The application communicates with the database using local time values. The locale is determine by the timezone name.
If the application needs to use absolute time values, it can set its timezone name to GMT. A list of valid timezone names can be found at Timezone Names. The database receives the local time values and the associated timezone name and stores them as absolute values in a compact form (11 bytes for datetime values, or 13 bytes for datetimeus values).

Datetime values

The application can specify a datetime value as:

Clients can use a couple of different formats to store and retrieve datetime values.

The simplest is a string, in "extended" ISO8601 format. The format is "YYYY-MM-DDThh:mm:ss.mmm <timezone string>"

It differs from ISO8601 in the usage of a timezone string rather than an offset from GMT. The minimum amount of information required is a date YYYY-MM-DD. The tail can be omitted, so YYYY-MM-DDThh, YYYY-MM-DDThh:mm, and so on. The colons ":" can be omitted as well, so YYYY-MM-DDThhmmss.mmm <timezone string> is valid as well.

The second application side datatype is a "packed C structure" representation of a datetime. The structure definition is:

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;

/* datetime type definition */
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;

/* interval types definition */
typedef struct cdb2_client_intv_ym {
    int                 sign;       /* sign of the interval, +/-1 */
    unsigned int        years;      /* interval year */
    unsigned int        months;     /* interval months [0-11] */
} cdb2_client_intv_ym_t;

typedef struct cdb2_client_intv_ds {
    int                 sign;       /* sign of the interval, +/-1 */
    unsigned int        days;       /* interval days    */
    unsigned int        hours;      /* interval hours   */
    unsigned int        mins;       /* interval minutes */
    unsigned int        sec;        /* interval sec     */
    unsigned int        msec;       /* msec             */
} cdb2_client_intv_ds_t;

typedef struct cdb2_client_intv_dsus {
    int                 sign;       /* sign of the interval, +/-1 */
    unsigned int        days;       /* interval days    */
    unsigned int        hours;      /* interval hours   */
    unsigned int        mins;       /* interval minutes */
    unsigned int        sec;        /* interval sec     */
    unsigned int        usec;       /* usec             */
} cdb2_client_intv_dsus_t;

where DB_MAX_TZNAME=36.


Interval overview

An interval type represents an amount of time (for example, 3 hours and 20 minutes).

Intervals come in two flavors:

The first type stores years and months (like 1 year and 10 months), while the latter stores days, hours, minutes, seconds and milliseconds/microseconds. The interval types are mutually incompatible (can't tell how many seconds are in a month).

Interval values are more or less indistinguishable from a quantity value. For example, it would be easy to store a year-to-month interval value as an integer, representing 12*years+months. The real advantage of storing time intervals in their own type comes from the SQL support for time arithmetic and decomposition.

Interval values

The datatype names for interval types are:

The intervals, both year-to-month and day-to-second, can be expressed in a several ways:

The "-" prefix followed by a space denotes a negative time interval (like "3hours ahead"). The day-to-second format allows shorter forms by omitting the tail. You are required to have at least the days and the following space to have the interval value parsed correctly). In the C structures used to bind values, the quantities are unsigned (i.e. positive) values, the sign being determined by the sign field. Example: -1msec will be expressed by sign=-1, days=hours=mins=sec=0, msec=1.

Local Time Values

The datetime field permits introducing local time values (i.e. wall-clock) into the database. The client specifies implicitly or explicitly the timezone location, and Comdb2 determines the "absolute" time value and stores. The same time value can be retrieved afterwards using the same timezone (in which case the same value will be returned) or a different timezone (in which case the value will be adjusted for the new timezone).


Let say somebody in NYC enters the time 01:00PM (as Eastern Standard Time). One second later someone in Seattle retrieves the same time (using Pacific Standard Time). The value retrieved is 10:01AM. This shows how the db handles automatically the timezone differences. Another client in Seattle could retrieve the data using NYC time (by specifying the EST timezone for that specific record) or any other zone. Two clients sharing same longitude coordinates, but obeying different daylight saving rules or different GMT offsets, are able to exchange time values in the same fashion (support for daylight savings and GMT offset anomalies).

Timezone specification

Each time the application stores or retrieves a datetime field, it does so using a local time value corresponding to a certain timezone. There are a couple of ways to set the timezone:

The order of precedence for determining the effective timezone name is:

While retrieving datetime values, you need to use either the implicit or the session based timezone to get the values in the expected timezone. If you use the implicit local timezone, the same code running on machines with different timezone values will end up inserting/fetching different values. For setting the timezone, keep in mind that SET statements are deferred. If you supply an invalid timezone, you won't get an error until the next statement that uses it.

Using datetimes and intervals in SQL

Examples in this section will use this table definition:

schema { 
        datetime timestamp                                                                                     

keys { 
        "UUID"      = uuid                                                                                         
        "TIMESTAMP" = timestamp                                                                            
        "UUID_TM"   = uuid+timestamp                                                              

Retrieving a datetime value

Inserting a datetime value


Intervals can be specified in sql using string or numerical format described above (the numerical format being number of months for year-to-month format and number of seconds.milliseconds for day-to-second format).

In addition, the following casting can be used to specify intervals (mostly used with datetime arithmetic):

SQL Arithmetic

The following operations (or a combinations of) are valid:


Time Decomposition/SQL Functions

Microsecond-precision Datetimes and Intervals

Comdb2 supports microsecond-precision datetime and interval types. The type names for these 2 types are datetimeus and intervaldsus, respectively.

datetimeus and intervaldsus can be considered as an extension of their corresponding millisecond-precision type, with larger fractional second precision.


cdb2sql> create csc2 table t { schema { datetimeus v } }
[create csc2 table t { schema { datetimeus v } }] rc 0
cdb2sql> insert into t values(now('us'))
(rows inserted=1)
[insert into t values(now())] rc 0
cdb2sql> insert into t values('2222-2-2T2:2:2.222222')
(rows inserted=1)
[insert into t values('2222-2-2T2:2:2.222222')] rc 0
cdb2sql> select * from t
(v="2016-06-09T180734.045056 America/New_York")
(v="2222-02-02T020202.222222 America/New_York")
[select * from t] rc 0

Expressing Datetimeus and Intervaldsus in String Format

String Format Precision Fraction Range
datetimeus YYYY-MM-DDThh:mm:ss[.ssssss] <timezone string> 6 digits. Excess precision is silently discarded 000000 - 999999
intervaldsus [- ]DAYS HH:MM:SS[.ssssss] <timezone string> 6 digits. Excess precision is silently discarded 000000 - 999999