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.

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 atop standard Java. 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")
)

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()")

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

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",
)

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()",
)

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 Java time types! For instance, it makes sense to add a Period to an Instant, 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",
)

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.

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`)")

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 massive performance differences 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."

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`)")

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 time zones:

// 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()

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.

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:

Specific date-times

Deephaven uses java.time.Instant to represent specific points in time in queries. An Instant is accurate to the nearest nanosecond.

To see the current date and time on your system, run the following:

println now()

This will print out a date and time in the default format, such as 2021-09-09T11:58:41.041000000 ET. The default format consists of the following fields:

yyyy-MM-ddThh:mm:ss.ffffff TZ

• yyyy - the year
• MM - the month
• dd - the day
• T - the separator between the date and time
• hh - the hour of the day
• mm - the minute of the hour
• ss - the second of the minute
• ffffff - the fraction of a second
• TZ - the time zone

Deephaven stores dates-times using the java.time.Instant class. Internally, this stores the date-time as a signed 64-bit long, which contains the number of nanoseconds since the Unix epoch (January 1, 1970, 00:00:00 GMT). You can create these directly (like in the code above) and use dates and times directly in the query language, including adding and subtracting them.

The following example shows the creation of two specific points in time, time1 and time2, exactly one year apart from each other. We then calculate the difference between these two date/time instances, resulting in 31,536,000,000,000,000 nanos (there are 31,536,000 seconds in a year).

time1 = parseInstant("2020-08-01T12:00:00 ET")
time2 = parseInstant("2021-08-01T12:00:00 ET")

timeDiff = diffNanos(time2, time1)
println timeDiff

Periods and Durations

Periods and durations represent spans of time that can be either positive or negative. A period represents a span of time that is greater than one day - lengths of time one would normally count on a calendar. A duration represents a span of time that is less than one day in length - spans of time one would normally count on a clock (but accurate down to the millisecond!). Deephaven uses Java's Period Duration classes.

Durations are prefixed by a PT, whereas Periods are prefixed by the letter P.

The following query demonstrates several query operations using periods and durations.

base_time = parseInstant("2023-01-01T00:00:00 UTC")

source = emptyTable(10).update("Timestamp = base_time + i * SECOND")
hour_duration = parseDuration("PT1H")
day_period = parsePeriod("P1D")
result1 = source.update("TS2 = plus(Timestamp, hour_duration)")
result2 = source.update("TS2 = plus(Timestamp, day_period)")
result3 = source.update("TS2 = Timestamp + 'PT1H'")
result4 = source.update("TS2 = Timestamp + 'P-1D'")

The above example creates a source table from an Instant called base_time. Each row of the source table is one second after the previous. A one-hour duration and a one-day period are then instantiated. From there, four resultant tables are derived from source:

• result1 uses the query language's built-in plus method to add hour_duration to Timestamp, so the TS2 column contains timestamps one hour after those in the Timestamp column.
• result2 uses the same built-in plus method, but adds day_period instead. Thus, TS2 contains timestamps one day after those in the Timestamp column.
• result3 uses the overloaded + operator to add a one hour duration to Timestamp again. Thus, TS2 looks identical to that in the result1 table.
• result4 uses the overloaded + operator to add a negative day-long period to Timestamp. Thus, TS2 looks identical to that in the result2 table.

Durations use the following suffixes for different units of time:

• -: Negative
• H: Hour
• M: Minute
• S: Second

Periods use the following suffixes for different units of time:

• - - Negative
• Y - Year
• M - Month
• D - Day

For example, a duration of PT1H1M-1S means one second short of one hour and one minute. Equivalently, a period of P1Y1M-1D means one day short of one year and one month.

The following example creates an instant, then adds a positive and negative period and duration.

time1 = parseInstant("2020-04-01T12:00:00 ET")

posPeriod = parsePeriod("P5D")
negPeriod = parsePeriod("P-5D")
posDuration = parseDuration("PT1H1M1S")
negDuration = parseDuration("PT-5H1M5S")


println plus(time1, posPeriod)
println plus(time1, negPeriod)
println plus(time1, posDuration)
println plus(time1, negDuration)

Time zones

Deephaven uses Java's TimeZone class to store time zone information. Time zones in instants use abbreviations, such as ET for US Eastern time, UTC or Z for coordinated universal time (UTC), etc.

println timeZone("ET")
println timeZone("PT")
println timeZone("UTC")

By default, Deephaven uses the US East time zone ("America/New_York") as the time zone for database operations. For example, when writing a CSV from a table which includes Instant columns, the New York time zone will be used unless a different time zone is passed in to the write_csv call.

The following example prints a time in the Denver time zone and in the New York time zone. The printed values end with Z (UTC), so the two-hour difference between the two time zones is apparent.

time1 = parseInstant("2021-01-01T01:00 ET")
time2 = parseInstant("2021-01-01T01:00 MT")

println time1
println time2

Predefined date-time variables

Deephaven provides several predefined date/time variables representing specified time periods in nanoseconds. These can be used in formulas.

Predefined date-time variables are:

• YEAR_365
• YEAR_AVG
• WEEK
• DAY
• HOUR
• MINUTE
• SECOND

The following example shows these variables and how to use them, both in a simple calculation and when manipulating a table. Notice that the timestamps in TS2 are one minute after the timestamps in the original table. A user will not typically call plus directly; the Deephaven Query Language will do that automatically when times are summed, as in the source/result part of the example.

println YEAR_365
println YEAR_AVG
println WEEK
println DAY
println HOUR
println MINUTE
println SECOND

time1 = parseInstant("2020-04-01T12:00:00 ET")
time2 = plus(time1, WEEK)
println time1
println time2

source = timeTable("PT1S")

result = source.update("TS2 = Timestamp + MINUTE")

Examples

This section contains an example set demonstrating the date and time methods described above.

First, we create a simple table with four rows. Note that all of our times are based in NY.

time1 = parseInstant("2020-04-01T09:00:00 ET")
time2 = parseInstant("2020-04-01T10:28:32 ET")
time3 = parseInstant("2020-04-01T12:00:00 ET")
time4 = parseInstant("2020-04-01T16:59:59 ET")

events = newTable(
   instantCol("EventDateTime", time1, time2, time3, time4),
   stringCol("Level", "Info", "Error", "Warning", "Info"),
   stringCol("Event", "System starting", "Something bad happened", "Invalid login", "System stopping"),
)

Filter to events after a certain time

The following query returns all events after 10AM 2020-04-01. Note that comparison operators on date-times are supported in query language strings. To use a comparison operator on a date-time string, the date-time string must be wrapped in single quotes ’.

eventsAfterTen = events.where("EventDateTime > '2020-04-01T10:00 ET'")

Filter to events within a range

The following example returns all events between 10AM - 4PM on 2020-04-01, using a formula within the query string.

eventsMiddleDay1 = events.where("EventDateTime >= '2020-04-01T10:00 ET' && EventDateTime <= '2020-04-01T16:00 ET'")

The following example returns all dates between 10AM - 4PM on 2020-04-01, using the inRange method.

eventsMiddleDay2 = events.where("inRange(EventDateTime, '2020-04-01T10:00 ET', '2020-04-01T16:00 ET')")

Related documentation

• How to use filters
• How to work with strings
• date-time
• update
• where