Timestamp formats

When creating a data source description for your source input data, you describe timestamp fields using the:

  • sqlType TIMESTAMP to denote that it is a timestamp; refer to Types of data
  • sourceFieldFormat to specify which timestamp format it is in

For example, if your timestamp is in epoch time format, in milliseconds, your description of the field may look something like this:

{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “TIMESTAMP_EPOCH_MILLIS”,
}

The GeoSpock database supports the following time formats:

For more information about creating a data source description, refer to Data source description files.

During ingest, the GeoSpock database converts timestamps to the UTC time zone for storage, so timestamps returned by queries are in UTC.

ISO 8601 format timestamps (TIMESTAMP_ISO_8601)

This time format supports RFC 3339, a subset of ISO 8601, with the following exceptions:

  • Leap seconds are not parsed. If you include leap seconds in your timestamps, the row of data will be rejected during ingest
  • The GeoSpock database ingestor parses any precision of decimal seconds, but only keeps 6 decimal places To describe a source input data field of this format, your property description will look something like this:
{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “TIMESTAMP_ISO_8601”,
}

The date/time format is based on RFC 3339 section 5.6, as follows:

Timestamp form Example
UTC "1985-04-12T23:20:50.52Z"
UTC - milliseconds "1985-04-12T23:20:50.123Z"
UTC - microseconds "1985-04-12T23:20:50.123456Z"
UTC - nanoseconds "1985-04-12T23:20:50.123456789Z"
No offset "2000-01-02T14:00:00+00:00"
No offset - milliseconds "2000-01-02T14:00:00.123+00:00"
No offset - microseconds "2000-01-02T14:00:00.123456+00:00"
No offset - nanoseconds "2000-01-02T14:00:00.123456789+00:00"
Minus offset "1996-12-19T16:39:57-08:00"
Plus offset "2017-11-14T00:00:00+08:00"
Plus offset - milliseconds "2017-11-14T00:00:00.123+08:00"
Plus offset - microseconds "2017-11-14T00:00:00.123456+08:00"
Plus offset - nanoseconds "2017-11-14T00:00:00.123456789+08:00"
00:00 offset "1999-12-31T16:39:57-00:00"

SQL format timestamps (TIMESTAMP_SQL)

The TIMESTAMP_SQL format timestamp is a temporal data type that holds a combination of date and time.

The format is YYYY-MM-DD HH:MM:SS which is fixed at 19 characters.

To describe a source input data field of this format, your property description will look something like this:

{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “TIMESTAMP_SQL”,
}

Be aware that the ingestor assumes that timestamps in this format are in the UTC time zone.

Epoch time format in milliseconds (TIMESTAMP_EPOCH_MILLIS)

This is an integer number of milliseconds since the Unix epoch (1970-01-01 00:00 UTC), ignoring leap seconds. For example the timestamp for 5/10/2019, 7:41:26.760 UTC would be represented like this 1557474086760.

To describe a source input data field of this format, your property description will look something like this:

{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “TIMESTAMP_EPOCH_MILLIS”,
}

Epoch time in seconds (TIMESTAMP_EPOCH_SECONDS)

This is an integer number of seconds since the Unix epoch (1970-01-01 00:00 UTC), ignoring leap seconds. For example, the timestamp for 5/10/2019, 7:41:26 UTC would be represented like this 1557474086.

To describe a source input data field of this format, your property description will look something like this:

{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “TIMESTAMP_EPOCH_SECONDS”,
}

Custom time formats (TIMESTAMP_CUSTOM)

This sourceFieldFormat enables you to parse timestamp formats that are not covered by the other presets. If your source input data fits one of the preset formats , it is strongly recommended that you use one of those formats. If you have a timestamp that complies with ISO 8601, you should use the TIMESTAMP_ISO_8601 sourceFieldFormat rather than a custom format to ensure that your timestamp is ingested correctly.

For this timestamp format, the ingestor uses a Java 8 SimpleDataFormat pattern string; for the specification of this string, refer to the section about Date and Time Patterns in the class documentation.

In the description for this field, specify the custom format, for example:

{
    “id”: “event_timestamp”,
    “sourceFieldName”: “date2”,
    “sqlType”: “TIMESTAMP”,
    “sourceFieldFormat”: “CUSTOM”,“sourceFieldCustomTimestampFormat”: “dd.MM.yyyy HH:mm z”
}

For example:

  • 31.01.2016 14:15 BST would be parsed with sourceFieldCustomTimestampFormat:"dd.MM.yyyy HH:mm z"
  • 31.01.2016, 14:15:32 contains a literal comma space, and would be parsed with sourceFieldCustomTimestampFormat:"dd/MM/yyyy', 'HH:mm:SS"

Be aware that:

  • if you specify a format that includes time zone information, the ingestor uses the parsed time zone to transform the timestamp into UTC before storing it in the GeoSpock database. If you do not include time zone information in the timestamp, the ingestor assumes it is UTC.
  • within date and time pattern strings, letters from A to Z and from a to z are interpreted as pattern letters representing the components of a date or time string. To quote text, use single quotes (') to avoid these letters being misinterpreted, for example: "'text'".

Parquet time format (TIMESTAMP_PARQUET)

This source format is used when the timestamps in your Parquet files are stored as the native Parquet timestamp type, and is therefore only compatible with source input data that is in Parquet format (format:PARQUET). Parquet files store data in their own binary format and have a timestamp type that represents UTC timestamps.