Using automatic timestamps

Track timestamps automatically using Automatic System Time

The Automatic System Time feature provides automatic GMT system timestamps, which are useful for time series data, such as automatically tracking the time of each record insert or update.

Some data sources do not capture the timestamp of data they collect. To simplify using such data, the FairCom Database Engine allows such data to be timestamped automatically. The auto-generated timestamp can be used to automatically track the insert and update times of a record.

The FairCom Database Engine provides functions to create fields automatically set to the system timestamp on an insert or update or both. Each field must be the autosystemtime type. The timezone of the timestamp is always GMT, which makes it easy to compare data from locations in different timezones.

Auto-generated timestamps may be used as the primary key of a record, although they do not guarantee uniqueness (they may cause errors when used as primary keys). Most operating systems cannot provide a timestamp with a fine enough level of precision to ensure uniqueness when inserting more than approximately 500 records a second.

When you configure tables for Automatic Data Aggregation through the cttimestamp.json file, the "timestampField" attribute applied to a field automatically enables auto-timestamps for that field, and you are not required to use any of the alternative API functions described below.

For direct application development, automatic timestamp functions are provided in the FairCom DB API and FairCom DB ISAM API (see tables below), as well as in the REST API.

Note

Timestamps are stored in Greenwich Mean Time (GMT), not local server time. GMT is the default offset (i.e. an offset of 0 hours and 0 minutes) because it provides a universal anchor for all time zones. When viewing a timestamp, a customer can change a timezone offset to any valid value.

Note

Automatic System Time assignment support has been added to c-tree APIs.

Hot Alter Table support

Support for ctdbAlterTable has been added to include Automatic System Timestamp features. This function can be used to:

Set/reset an existing field to be the auto system time.
Change the auto system time feature of a field.
Add a new field with the auto system time.
Delete a field with the auto system time.
During Alter Table, when setting/resetting auto system time, the field content is not modified, i.e., the current timestamp is not applied during Alter Table to existing record contents.

API Support

Table 1. FairCom DB API

Function	Description
ctdbAlterTable	Utilize hot alter table as described above
ctdbSetFieldAutoSysTime	Sets a field to receive a timestamp automatically
ctdbGetFieldAutoSysTime	Gets the auto timestamp attributes of a field
ctdbCurrentTimestamp	Returns the timestamp of a
ctdbCurrentDateTimeUTC	Set a DATETIME field with the current UTC time with millisecond precision

Table 2. FairCom DB ISAM API

Function	Description
AddAutoSysTimeFields	Adds an auto timestamp to one or more fields
WhichAutoSysTimeFields	Returns which fields have an auto timestamp
RemoveAutoSysTimeFields	Removes all auto timestamps from a file
UpdateAutoSysTimeFields	Updates the auto timestamp configuration on one or more fields

Automatic data aggregation simplifies the task of performing simple processing on the timestamped data

Automatic timestamps, system time, GMT timestamps, time series data, record insert time, record update time, FairCom Database Engine, autosystemtime type, cross-timezone data, primary key, cttimestamp.json, FairCom DB API, FairCom DB ISAM API, REST API, Hot Alter Table, ctdbAlterTable, ctdbSetFieldAutoSysTime, ctdbGetFieldAutoSysTime, ctdbCurrentTimestamp, ctdbCurrentDateTimeUTC, AddAutoSysTimeFields, WhichAutoSysTimeFields, RemoveAutoSysTimeFields, UpdateAutoSysTimeFields.