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.

TypeIndexedNullableDescriptionStored as
datetimeYes (Default)Yes (except Primary Column)A string-based representation of a moment in time, e.g. Mon Jan 2 15:04:05 -0700 2006.
*Supported range of values:
[1970-01-01 00:00:00, 2106-02-07 06:28:15]
datetime or datetime64 based on resolution. See below for more information.
datetime64Yes (Default)Yes (except Primary Column)A string-based representation of a moment in time, e.g. Mon Jan 2 15:04:05 -0700 2006.
Supported range of values:
[1925-01-01 00:00:00, 2283-11-11 23:59:59.99999999]
datetime or datetime64 based on resolution. See below for more information.
epochYes (Default)Yes (except Primary Column)Converted to a datetime or datetime64 based on additional formatting information.datetime or datetime64 based on resolution. See below for more information.

Primary

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

📘

Primary

Any dataset being imported must have at least one datetime or epoch column set as the primary.

Format and Resolution

Transform column definitions that use either the datetime, datetime64 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. Seconds are stored as DateTime, milliseconds as DateTime64

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 and datetime64 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:

GO FormatExample Java FormatExample data.Description
2006-01-02YYYY-MM-DD2022-11-25Date
15:04:05HH:mm:ss18:59:30Time
2006-01-02T15:04:05YYYY-MM-DD'T'HH:mm:ss2022-11-25T18:59:30Date with hours, minutes seconds
2006 Jan 02 15:04:05YYYY MMM DD HH:mm:ss2022 Nov 25 18:59:30Short month space separated
06-Jan-02 15:04:05YY-MMM-DD HH:mm:ss22-Nov-25 18:59:30Short year Hyphen separated
2006-01-02T15:04:05.999YYYY-MM-DD'T'HH:mm:ss.SSS2022-11-25T18:59:30.326Datetime with milliseconds (ISO Datetime)
2006-01-02T15:04:05-0700YYYY-MM-DD'T'HH:mm:ssZZZZ2022-11-25T18:59:30 -0700Datetime with offset (ISO offset)
2006-01-02 15:04:05MSTYYYY-MM-DD HH:mm:sszzz2022-11-25 18:59:30 PDTIncluding zone (ISO zoned)
2006-01-02 15:04:05 MonYYYY-MM-DD HH:mm:ss E2022-11-25 18:59:30 FriIncluding short day of week
2006-01-02 15:04:05 MondayYYYY-MM-DD HH:mm:ss E2022-11-25 18:59:30 FridayIncluding long day of week
06-01-02 3:4:5YY-MM-DD h:m:s22-11-25 6:59:30Short date and short time
2006-01-02 3:4:5 PMYYYY-MM-DD HH:mm:ss a2022-11-25 6:59:30 PMLong date and short time with AM/PM designator.

📘

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 has extended 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:

FormatMeaning
nsnanoseconds since epoch
usmicroseconds since epoch
msmilliseconds since epoch
cscentiseconds since epoch
sseconds since epoch (a.k.a. Unix time)

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](/docs/transform-structure#settings-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, resolution greater than seconds is stored as a max resolution of milliseconds and DateTime64