Work with time

This guide discusses working with time in Deephaven. Deephaven is a real-time data platform that handles streaming data of various formats and sizes, offering a unified interface for the ingestion, manipulation, and analysis of this data. A significant part of the Deephaven experience involves working with data that signifies specific moments in time: calendar dates, time-periods or durations, time zones, and more. These can appear as data in Deephaven tables, literals in query strings, or values in Groovy scripts. So, it is important to learn about the best tools for these different jobs.

Because time-based data types are integral to working with Deephaven, understanding their representations and how to manipulate them is critical. This will be true for both server-side applications and client-side applications. As such, the date-time types natively supported in Deephaven are a good starting point for this discussion.

Natively supported date-time types

The Deephaven query engine is responsible for executing a query and updating the query results as data changes. The engine is implemented in Java. As a result, all of the date-time types that are natively supported by the Deephaven engine are Java types. Java's java.time package provides types for representing time-related data.

The java.time types natively supported by Deephaven are:

Type	Description
java.time.ZoneId	A ZoneId represents a time zone such as "Europe/Paris" or "CST".
java.time.LocalDate this link on	A LocalDate is a date without a time zone in the ISO-8601 system, such as "2007-12-03" or "2057-01-28".
java.time.LocalTime	LocalTime is a timestamp without a date or time zone in the ISO-8601 system, such as "10:15:30", "13:45:30.123456789". LocalTime has nanosecond resolution.
java.time.Instant	An Instant represents an unambiguous specific point on the timeline, such as 2021-04-12T14:13:07 UTC. Instant has nanosecond resolution.
java.time.ZonedDateTime	A ZonedDateTime represents an unambiguous specific point on the timeline with an associated time zone, such as 2021-04-12T14:13:07 America/New_York. ZonedDateTime has nanosecond resolution. A ZonedDateTime is effectively an Instant with a ZoneId.
java.time.Duration	A Duration represents a duration of time, such as "5.5 seconds". Duration has nanosecond resolution.
java.time.Period	A Period is a date-based amount of time in the ISO-8601 system, such as "P2y3m4d" (2 years, 3 months and 4 days).

The Deephaven Query Language

All of Deephaven's natively-supported types are Java types, which integrate seamlessly with Deephaven's Groovy API. Java types can easily be created and manipulated with the Deephaven Query Language.

The Deephaven Query Language (DQL) is the primary way of expressing commands directly to the query engine. It is responsible for translating the user's intention into compiled code that the engine can execute. DQL is written with strings - called query strings - that can contain a mixture of Java and Groovy code. Thus, DQL query strings are the entry point to a universe of powerful built-in tools. These query strings are often used in the context of table operations, like creating new columns or applying filters. Here's a simple DQL example:

t = (
    emptyTable(10)
    .update("Col1 = ii", "Col2 = Col1 + 3", "Col3 = 2 * Math.sin(Col1 + Col2)")
    .where("Col1 % 2 == 0", "Col3 > 0")
)

t

note

For more on the DQL, refer to guides on writing basic formulas, working with strings, using built-in functions, and more.

There are four important tools provided by DQL that are relevant to the discussion on date-time types.

1. Built-in Java functions

Deephaven has a collection of built-in functions that are useful for working with date-time types. For the sake of performance, these functions are implemented in Java. DQL supports calling these functions directly in query strings, opening up all of Deephaven's built-in Java functions to the Groovy interface. The following example uses the built-in Deephaven function now to get the current system time as a Java Instant:

t = emptyTable(5).update("CurrentTime = now()")

t

note

now uses the current clock of the Deephaven engine. This clock is typically the system clock, but it may be set to a simulated clock when replaying tables.

These functions can also be applied to columns, constants, and variables. This slightly more complex example uses the built-in Deephaven function epochDaysToLocalDate to create a LocalDate from a long that represents the number of days since the Unix epoch:

t = emptyTable(5).update("DaysSinceEpoch = ii", "LocalDateColumn = epochDaysToLocalDate(DaysSinceEpoch)")

t

In addition to functions, Deephaven offers many built-in constants to simplify expressions involving time values. These constants, like SECOND, DAY, and YEAR_365, are equal to the number of nanoseconds in a given time period. Many of Deephaven's built-in functions operate at nanosecond resolution, so these constants provide a simple way to work with nanoseconds:

t = emptyTable(1).update(
        "CurrentTime = now()",
        "NanosSinceEpoch = epochNanos(CurrentTime)",
        "SecondsSinceEpoch = NanosSinceEpoch / SECOND",
        "MinutesSinceEpoch = NanosSinceEpoch / MINUTE",
        "DaysSinceEpoch = NanosSinceEpoch / DAY",
        "YearsSinceEpoch = NanosSinceEpoch / YEAR_AVG",
)

t

2. Java object methods

Both Groovy and Java are object-oriented programming languages. As such, all Java objects have associated methods. These methods can be called in query strings. Here is an example that builds upon the previous example, and uses the getDayOfWeek method bound to each LocalDate object to extract the day of the week for each LocalDate:

t = emptyTable(5).update(
        "DaysSinceEpoch = ii",
        "LocalDateColumn = epochDaysToLocalDate(DaysSinceEpoch)",
        "DayOfWeek = LocalDateColumn.getDayOfWeek()",
)

t

To be clear:

• DaysSinceEpoch is a 64-bit integer.
• LocalDateColumn is a Java LocalDate object.
• epochDaysToLocalDate is a Java function from the built-in Deephaven library.
• DayOfWeek is the return value of getDayOfWeek, a Java method bound to the LocalDate class.

3. Arithmetic and inequality operators

Query strings support syntactic sugar for special operators such as +, -, >, <, >=, etc. for time types! For instance, it makes sense to add a Period or a Duration to an Instant, or to multiply a Duration by an integer. This example uses the built-in parsePeriod and parseDuration functions to create period and duration columns from strings. Then, the overloaded addition operator + is used to add them to the Timestamp column, and the overloaded multiplication operator * is used to create a column with timestamps that increment daily:

t = emptyTable(5).update(
        "Timestamp = now()",
        "PeriodColumn = parsePeriod(`P1D`)",
        "DurationColumn = parseDuration(`PT24h`)",
        "TimePlusPeriod = Timestamp + PeriodColumn",
        "TimePlusDuration = Timestamp + DurationColumn",
        "IncreasingTime = Timestamp + PeriodColumn * i",
)

t

note

This example uses backticks to represent strings. For more info, see the guide on working with strings.

4. Date-times using DQL

In Deephaven, date-time values can be expressed using very simple literal syntax. These literal values can be used directly in query strings or as string inputs to built-in functions.

tip

In query strings, time literals are denoted with single quotes.

This example creates Duration columns from a time literal as well as from a string parsed by parseDuration:

t = emptyTable(5).update("DurationFromLiteral = 'PT24h'", "DurationFromString = parseDuration(`PT24h`)")

t

note

Note the difference in single quotes for the time literal and back quotes for the string.

Using query string time literals can yield more compact and more efficient code. In the prior example, parseDuration(`PT24h`) is evaluated for every single row in the table, but here 'PT24h' is only evaluated once for the entire table. This can lead to performance improvements for large tables:

t1Start = System.nanoTime() / 1000000000
t1 = emptyTable(100000000).update("DurationColumn = parseDuration(`PT24h`)")
t1End = System.nanoTime() / 1000000000

t2Start = System.nanoTime() / 1000000000
t2 = emptyTable(100000000).update("DurationColumn = 'PT24h'")
t2End = System.nanoTime() / 1000000000

t1Time = t1End - t1Start
t2Time = t2End - t2Start
println "Using built-in parse function: " + t1Time + " seconds."
println "Using literal: " + t2Time + " seconds."

t1

t2

Log

Most of the seven key Java types can be created using literals or functions like parseDuration:

// ZoneId columns can be created with literals or the parseTimeZone built-in.
// The literal or string argument must be a valid Java time zone.
t1 = emptyTable(1).update("TimeZone1 = 'GMT'", "TimeZone2 = parseTimeZone(`America/New_York`)")

// LocalDate columns can be created with literals or the parseLocalDate built-in.
// The literal or string argument must use the ISO-8601 date format 'YYYY-MM-DD'.
t2 = emptyTable(1).update("LocalDate1 = '2022-03-28'", "LocalDate2 = parseLocalDate(`2075-08-08`)")

// LocalTime columns can be created with literals or the parseLocalTime built-in.
// The literal or string argument must use the ISO-8601 time format 'hh:mm:ss[.SSSSSSSSS]'.
t3 = emptyTable(1).update("LocalTime1 = '10:15:30.46'", "LocalTime2 = parseLocalTime(`12:01:01.4567`)")

// Instant columns can be created with literals or the parseInstant built-in.
// The literal or string arguments must use the ISO-8601 date-time format `yyyy-MM-ddThh:mm:ss[.SSSSSSSSS] TZ`.
t4 = emptyTable(1).update(
        "Instant1 = '2015-01-30T12:34:56Z'",
        "Instant2 = parseInstant(`2023-11-21T21:30:45Z`)",
)

// ZonedDateTime columns cannot be created with literals, as they are indistinguishable from Instant literals.
// ZonedDateTime columns can be created with the parseZonedDateTime built-in,
// or by localizing the time zone of an Instant with the atZone() method.
// The string arguments must use the ISO-8601 date-time format `yyyy-MM-ddThh:mm:ss[.SSSSSSSSS] TZ`.
t5 = emptyTable(1).update(
        "ZonedDateTime1 = parseZonedDateTime(`2021-11-03T01:02:03 GMT`)",
        "ZonedDateTime2 = '2023-11-21T21:30:45 GMT'.atZone('GMT')",
)

// Duration columns can be created with literals or the parseDuration built-in.
// The literal or string arguments must use the ISO-8601 duration format 'PnDTnHnMn.nS'.
// Negative durations are represented as 'P-nDT-nH-nM-n.nS' or '-PnDTnHnMn.nS'.
t6 = emptyTable(1).update("Duration1 = 'PT6H30M30S'", "Duration2 = parseDuration(`PT10H`)")

// Period columns can be created with literals or the parsePeriod built-in.
// The literal or string arguments must use the ISO-8601 period format 'PnYnMnD'.
// Negative periods are represented as 'P-nY-nM-nD' or '-PnYnMnD'.
t7 = emptyTable(1).update("Period1 = 'P2Y3M10D'", "Period2 = parsePeriod(`P10Y0M3D`)")

t1

t2

t3

t4

t5

t6

t7

Put it all together

To illustrate the power and ease of working with natively supported date times, this example uses time literals, operator-overloaded arithmetic, and Java time methods together to create timestamps, compute time differences, and extract information about those timestamps in Tokyo and New York timezones:

// Create reference time and timestamp column using operator overloading and Java method multipliedBy()
t = emptyTable(100).update(
        "Reference = '2025-10-15T13:23 ET'",
        "Timestamp = Reference + 'PT1h'.multipliedBy(ii)",
)

// create a simple closure
f = { java.time.Instant a, java.time.Duration b -> a - b }

// Use operator overloading and diffMinutes() built-in to get time since reference time
t = t.update(
        "DifferenceNanos = Timestamp - Reference",
        "DifferenceMin = diffMinutes(Reference, Timestamp)",
)

// Finally, use built-in functions and time zone literals to get date and
// day of week in two different time zones
t = t.update(
        "DateTokyo = toLocalDate(Timestamp, 'Asia/Tokyo')",
        "DayTokyo = dayOfWeek(Timestamp, 'Asia/Tokyo')",
        "DateNY = toLocalDate(Timestamp, 'America/New_York')",
        "DayNY = dayOfWeek(Timestamp, 'America/New_York')",
        "TimeMinus1H = f(Timestamp, 'PT1h')",
)

// Assess metadata to see that types are as expected
tMeta = t.meta()

t

tMeta

Hopefully, it's apparent that working with date-times in Deephaven queries is a breeze. With all of the built-in time functions, most date-time operations can be accomplished with native Deephaven operations.