Open topic with navigation

TIBCO ActiveSpaces® - Enterprise Edition 5.0.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® - 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:

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

Note: 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 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'

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveSpaces® - Enterprise Edition

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