Timestamp Data Types

The Hydrolix platform was specifically designed for time-based records, and every Hydrolix table is required to have at least one (primary) timestamp field.

The following timestamp representations are supported.

Primary

Every Hydrolix table must contain at least one datetime or epoch field designated as the "primary" timestamp. This is used as the primary means of partitioning data for storage and retrieval.

Format and Resolution

Transform column definitions that use either the datetime or epoch datatypes require two additional attributes: format and resolution.

• format informs Hydrolix's ingestion services on how they should interpret the datetime data being ingested.
• resolution informs Hydrolix of the granularity at which the data should be stored, milliseconds or seconds. Milliseconds are the maximum that Hydrolix can currently store.

By default, datetime and epoch fields are always indexed.

{
    "name": "primaryTimestamp",
    "datatype": {
        "type": "datetime",
        "primary": true,
        "format": "2006-01-02 15:04:05",
        "resolution": "seconds"
    }
},
{
    "name": "anotherTimestamp",
    "datatype": {
        "type": "datetime",
        "format": "2006-01-02 15:04:05",
        "resolution": "ms"
    }
},
{
    "name": "anEpoch",
    "datatype": {
        "type": "epoch",
        "format": "ms",
        "resolution": "ms"
    }
},

datetime Format Options

The datetime datatype allows most time representations in incoming datetime data, provided that you can describe that format in the Go-style time format.

In the column definition format property, you must indicate how the data source would be represented in Go's "reference time" of 03:04:05 PM on January 2, 2006, in the Mountain Standard Time zone (UTC-07:00).

Some examples:

📘

Decimal seconds.

Be careful of the .000 and .999 designations for nanos, millis etc. Go interprets these differently. From the Go site https://pkg.go.dev/time:

// Fractional seconds can be printed by adding a run of 0s or 9s after
// a decimal point in the seconds value in the layout string.
// If the layout digits are 0s, the fractional second is of the specified
// width. Note that the output has a trailing zero.
do("0s for fraction", "15:04:05.00000", "11:06:39.12340")
// If the fraction in the layout is 9s, trailing zeros are dropped.
do("9s for fraction", "15:04:05.99999999", "11:06:39.1234")

We understand that the Go format can be a little tricky. If you want to test your datetime format the go site allows you to build and test out datetime in Go format.

You can use the code below to test your dates.

package main

import (
	"fmt"
	"time"
)

func main() {
	layout := "2006-01-02T15:05:05Z"
	data := "2022-01-04T16:39:49Z"
	t, err := time.Parse(layout, data)
	fmt.Println(t, err)
}

Regexp Format Options

In addition to the above format, Hydrolix extends the functionality provided by format allowing extended parsing of datetime data. This functionality uses a (similar to) regex functionality to interpret an incoming datetime.

This is based on the Go Regexp package and will take the value from the string and internally convert that into a datetime. We know this is not beautiful, but it does provide extensive flexibility, with limited performance impact to data ingestion.

The value capture names are: year, month, day, hour, minute, second, millisecond, microsecond and nanosecond

For example the timestamp 20220324201841373 can be ingested using the following format column.

{
  "type": "datetime",
  "resolution": "sec",
  "format": "(?P<year>[0-9]{4})(?P<month>[0-9]{2})(?P<day>[0-9]{2})(?P<hour>[0-9]{2})(?P<minute>[0-9]{2})(?P<second>[0-9]{2})(?P<millisecond>[0-9]{3})"
}

epoch Format Options

Use the epoch datatype for Unix-style "epoch time", or other representations of a timestamp a number of time-units that have passed since 1970-01-01 00:00:00 UTC, a.k.a. the epoch.

With epoch, acceptable values of format include the following:

In all cases, the Hydrolix ingester accepts either integers or numerical strings as legal epoch data. Hydrolix treats all epoch-based timestamps as UTC time.

Resolution

Hydrolix supports storing time values with either second or millisecond resolution. You can set this granularity level for a given field by specifying a resolution property within the appropriate output_columns definition, as discussed in Output Columns.

If for example you've set a given column to have millisecond resolution, and incoming data for that field includes a time down to the microsecond, Hydrolix will store the value after rounding down to the nearest millisecond.

Hydrolix uses seconds as its default stored-time resolution.

Seconds resolution will cause the data to be stored as datetime.