tibDateTime

Using tibDateTime Columns with tibdg and the Client API

ActiveSpaces tables can be defined by using tibdg with columns of the datetime type that are mapped internally to a tibDateTime object for storing data into rows of the table as shown in the following example:

tibdg table create mytable empid long
column create mytable startdate datetime

Columns that are mapped internally to tibDateTime can also be used as primary keys or secondary indexes. For example, to define a secondary index using the startdate column created in the previous tibdg command, use the following command:

tibdg index create mytable myindex startdate

For more information on using tibdg to define tables and indexes, see the topic "Defining a Table" in TIBCO ActiveSpaces® Enterprise Edition Administration.

After the table and indexes are defined, you can use the ActiveSpaces Client API to store values into and retrieve values from columns defined as datetime. For example, to use the Client API to store values into a row of a table, use the following C API function:

void tibdgRow_SetDateTime(tibEx e, tibdgRow row, const char *columnName, const tibDateTime *value)

To use the Client API to retrieve values from a row of a table, use the following C API function:

tibDateTime* tibdgRow_GetDateTime(tibEx e, tibdgRow row, const char* columnName)

Populating tibDateTime

Windows and Unix platforms use different structures for retrieving date/time data. The following are examples of how you can get the current date and time on each platform and populate a tibDateTime object:

#include <sys/types.h>
#include <sys/time.h>
#include “tibdg/tibdg.h”

struct timeval    timebuffer;
(void) gettimeofday(&timebuffer, NULL);

tibDateTime dt;
dt.sec  = timebuffer.tv_sec;
dt.nsec = timebuffer.tv_usec * 1000;
					

#include <sys/timeb.h>
#include “tibdg/tibdg.h”

truct __timeb64 timebuffer;
ftime64_s(&timebuffer);

ibDateTime dt;
t.sec  = timebuffer.time;
dt.nsec = timebuffer.millitm * 1000000;
				

Using tibDateTime Columns with SQL Commands

You can use tibDateTime columns with SQL commands.

The SQL CREATE TABLE command can be used to define a table with columns that map internally to a tibDateTime object by using the following SQL data types:

• DATETIME

• DATE

• TIME

• TIMESTAMP

The following SQL CREATE TABLE command is an example of creating a table with a column of DATETIME type:

CREATE TABLE IF NOT EXISTS mytable (empid BIGINT PRIMARY KEY, startdate DATETIME)

The following SQL CREATE INDEX command is an example of defining a secondary index for a table that includes a DATETIME column:

CREATE INDEX IF NOT EXISTS myindex ON mytable (startdate)

Populating tibDateTime Columns

After you have created a table and defined its indexes, you can store the data into a DATETIME column as follows:

INSERT INTO mytable (empid, startdate) VALUES (1, '2020-09-20 00:00:00.000000000Z') 

YYYY-DD-MM HH:MM:SS.SSSSSSSSSZ is the default format of a date or time string that is used with SQL commands.

To facilitate inserting data, ActiveSpaces also supports a subset of ISO 8601 date or time strings. These strings are in the following format:

 YYYY-MM-DD[Thh.mm.ss[.s][TZD]]

where

T is used to delimit the date from the time.

.s indicates fractional seconds and can be up to nine digits long.

A ".'' by itself is not valid.

TZD is the time zone designator.

ActiveSpaces supports storing time only in UTC, so TZD can be one of the following values:

• Z

• +00:00

• +0000

• +00

Therefore, the following INSERT statement is equivalent to the previous one mentioned in this topic:

INSERT INTO mytable (empid, startdate) VALUES (1, '2020-09-20')

Query tibDateTime Columns

Data stored in tibDateTime columns can be queried using a SQL SELECT statement as follows:

SELECT * FROM mytable WHERE startdate='2020-09-20 00:00:00.000000000Z'

When querying tibDateTime columns, you must always use the default date or time string format. The only exception to this rule is when you are trying to retrieve a single row of a table directly by using only its primary key. In such a case, you can use a date/time string in the ISO 8601 format mentioned in the tibDateTime section. For example, if you had defined your table using the following command:

CREATE TABLE mytable (empid BIGINT, startdate DATETIME, PRIMARY KEY (empid, startdate))

then in the WHERE clause of your SELECT statement, you can use an ISO 8601 formatted date/time string as shown in the following example:

SELECT * FROM mytable WHERE empid=1 AND startdate='2020-09-20'

or

SELECT * FROM mytable WHERE empid=1 AND startdate='2020-09-20T00:00:00+00:00'