Timestamp Data Types

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

The ingestion system accepts a wide variety of string and numerical formats and stores the parsed timestamps at either second- or millisecond-fidelity.

Ingestion types

Type	Indexed	Nullable	Resolution	Stored as
datetime	Yes (Default)	Yes	s, seconds	datetime
datetime	Yes (Default)	Yes	ms, milliseconds	datetime64
epoch	Yes (Default)	Yes	s, seconds	datetime
epoch	Yes (Default)	Yes	ms, milliseconds	datetime64

A valid datetime type is a string-based representation of a moment in time between 1970-01-01 00:00:00 and 2106-02-07 06:28:15, for example, Mon Jan 2 15:04:05 -0700 2006.

A valid epoch type is a number or string, for example, 1136239445 seconds or 1136239445346923967 nanoseconds since the epoch.

Primary key column

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

A timestamp used for the primary key must always have a value. A NULL is invalid and forbidden in the primary column and is rejected.

Format and resolution

The format and resolution attributes are required on all datetime or epoch columns in a column definition.

• format describes how to interpret, parse, or convert incoming data to a datetime or epoch
• resolution, determines the fidelity to retain and store, see Resolution

By default, timestamp fields are always indexed.

Timestamp column definitions with format and resolution

{
    "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"
    }
},

Basic datetime formats

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, and other time units. Go interprets these differently. See 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 by modifying the format and the string example and calling it with go run datetime-tester.go.

package main

import (
    "fmt"
    "os"
    "time"
)

func main() {
    layout := "2006-01-02T15:05:05Z"
    data := "2022-01-04T16:39:49Z"
    t, err := time.Parse(layout, data)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Error: %v\n", err)
        os.Exit(1)
    } else {
        fmt.Println("OK", t)
    }
}

Regexp format Options

Use a Go Regexp to handle unusual time formats not supported by Go's Basic datetime formats.

Hydrolix extends the functionality of format by 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. This provides 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.

Regexp format example

{
  "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})"
}

Regex for Google LogEntry Timestamps

Google products like LogEntry send timestamp data to the millisecond that's Z-normalized using 0, 3, 6, or 9 fractional digits. This means the same incoming timestamp may be formatted to six digits as in the case of 2025-07-30T07:49:17.275004Z or zero digits as in the case of 2025-07-30T07:49:17Z. This data can be successfully stored in Hydrolix using the following format regex:

Transform regex to ingest Google LogEntry-formatted timestamps

{
    "name": "googleTimestamp",
    "datatype": {
        "type": "datetime",
        "resolution": "ms",
        "format": "(?P<year>[0-9]{4})-(?P<month>[0-9]{2})-(?P<day>[0-9]{2})T(?P<hour>[0-9]{2}):(?P<minute>[0-9]{2}):(?P<second>[0-9]{2}).?(?P<millisecond>[0-9]{0,9})Z"
    }
}

If the incoming JSON object has the timestamp nested as in the following:

Google LogEntry JSON object

  {
    "message": {
      "attributes": {
        "logging.googleapis.com/timestamp": "2025-06-17T14:32:03.027425Z"
      }
    }
  }

then adding datatype.source.from_json_pointers to the transform will extract the timestamp value from the correct key. The literal / in the logging.googleapis.com/timestamp key must be replaced by ~1 in the transform. See Format Options: JSON for more on the from_json_pointers key.

Transform which extracts timestamp from a nested JSON structure

{
  "datatype": {
    "source": {
      "from_json_pointers": [
        "/message/attributes/logging.googleapis.com~1timestamp"
      ]
    }
  }
}

Combining with the previous transform configuration, this gives a complete configuration which extract a Google LogEntry-formatted timestamp from the key message.attributes.logging.googleapis.com/timestamp to be:

Complete Google LogEntry timestamp configuration

{
    "name": "googleTimestamp",
    "datatype": {
        "type": "datetime",
        "resolution": "ms",
        "format": "(?P<year>[0-9]{4})-(?P<month>[0-9]{2})-(?P<day>[0-9]{2})T(?P<hour>[0-9]{2}):(?P<minute>[0-9]{2}):(?P<second>[0-9]{2}).?(?P<millisecond>[0-9]{0,9})Z",
        "source": {
          "from_json_pointers": [
            "/message/attributes/logging.googleapis.com~1timestamp"
          ]
        }
    }
}

Accepted epoch formats

Use the epoch datatype for Unix epoch time, or similar representations of the number of time units that have passed since 1970-01-01 00:00:00 UTC, sometimes called the epoch.

With an epoch type, 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, Hydrolix ingestion accepts integers or numerical strings as legal epoch data and treats all epoch-based timestamps as UTC time.

Resolution

Hydrolix supports storing time values with either second or millisecond resolution. Setting resolution on an output_columns definition, as described in Output Columns, determines the storage data type.

Resolution	Is default?	Storage format	Accepted synonyms
seconds	Yes	datetime	seconds, second, secs, sec, s
milliseconds	No	datetime64	milliseconds, millisecond, millis, milli, ms

Storing at millisecond resolution uses more storage to retain the fidelity.

Higher fidelity timestamps are truncated to the specified resolution. For example, if a column has millisecond resolution, and the incoming format includes microseconds, the ingestion system stores the value after rounding down to the nearest millisecond.

Set a column's resolution explicitly to retain millisecond fidelity

Hydrolix doesn't infer resolution from format. Setting the format has no effect on resolution which determines storage data type.

For example, a transform configured with type epoch and format ms, but lacking an explicit resolution defaults to the lower fidelity resolution of seconds.

The highest fidelity that Hydrolix can store in any timestamp type column is millisecond in the datetime64 storage data type.