Timestamp Data Types

Timestamps

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.

Type

Indexed

Nullable

Description

Stored as

datetime

Yes (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.

datetime64

Yes (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.

epoch

Yes (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 Format

Example Java Format

Example data.

Description

2006-01-02

YYYY-MM-DD

2022-11-25

Date

15:04:05

HH:mm:ss

18:59:30

Time

2006-01-02T15:04:05

YYYY-MM-DD'T'HH:mm:ss

2022-11-25T18:59:30

Date with hours, minutes seconds

2006 Jan 02 15:04:05

YYYY MMM DD HH:mm:ss

2022 Nov 25 18:59:30

Short month space separated

06-Jan-02 15:04:05

YY-MMM-DD HH:mm:ss

22-Nov-25 18:59:30

Short year Hyphen separated

2006-01-02T15:04:05.999

YYYY-MM-DD'T'HH:mm:ss.SSS

2022-11-25T18:59:30.326

Datetime with milliseconds (ISO Datetime)

2006-01-02T15:04:05-0700

YYYY-MM-DD'T'HH:mm:ssZZZZ

2022-11-25T18:59:30 -0700

Datetime with offset (ISO offset)

2006-01-02 15:04:05MST

YYYY-MM-DD HH:mm:sszzz

2022-11-25 18:59:30 PDT

Including zone (ISO zoned)

2006-01-02 15:04:05 Mon

YYYY-MM-DD HH:mm:ss E

2022-11-25 18:59:30 Fri

Including short day of week

2006-01-02 15:04:05 Monday

YYYY-MM-DD HH:mm:ss E

2022-11-25 18:59:30 Friday

Including long day of week

06-01-02 3:4:5

YY-MM-DD h:m:s

22-11-25 6:59:30

Short date and short time

2006-01-02 3:4:5 PM

YYYY-MM-DD HH:mm:ss a

2022-11-25 6:59:30 PM

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

Format

Meaning

ns

nanoseconds since epoch

us

microseconds since epoch

ms

milliseconds since epoch

cs

centiseconds since epoch

s

seconds 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


Did this page help you?