Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# tibDateTime

Date and time information can be stored in ActiveSpaces as a `tibDateTime` type as an alternative to storing a date as a `string`, `long`, or `double` value. tibDateTime consists of two 64-bit integers; one for the number of seconds since January 1, 1970 (Unix epoch), and one for the number of nanoseconds after the time that the sec component denotes.
Note: ActiveSpaces does not support time zone information as a part of date and time values. In ActiveSpaces, `tibDateTime` values are UTC time. When inserting a date or time string into a column defined with `datetime` data type, an error occurs if a non-UTC time zone is used.

## 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® 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:

Unix platforms

    #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;

Windows

    #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;

The ActiveSpaces API can then be used to store the tibDateTime object into a row that is then stored into a table in the data grid.

## 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 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 provides support for 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

A space is not allowed between the time and TZD.

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 directly retrieve a single row of a table 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 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'

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.