Work with time

This guide shows how to work with dates, times, durations, periods, and time zones in Deephaven. Deephaven is a real-time data platform that handles streaming data of various formats and sizes, with a unified interface for ingesting, manipulating, and analyzing it. Much of that data marks specific moments in time, so date-times are central to working in Deephaven. These values appear as data in tables, as literals in query strings, and as values in Groovy scripts.

This guide covers the Java date-time types the engine supports natively, the query-language tools for working with them, filtering tables by time, and using date-times directly in Groovy scripts.

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 the date-time types the Deephaven engine supports natively are Java types. Java's java.time package provides types for representing time-related data.

This guide focuses on seven java.time types that Deephaven supports natively. The rest of this guide calls them the key Java types:

Periods and durations represent spans of time that can be either positive or negative. A period is a date-based amount of time, measured in years, months, weeks, and days. Periods are the spans you count on a calendar. A duration is a time-based amount of time, measured in days, hours, minutes, seconds, and fractions of a second down to the nanosecond. Durations are the spans you count on a clock, and a day in a duration is always exactly 24 hours.

The Deephaven Query Language

All of Deephaven's natively supported types are Java types. Groovy runs on the Java Virtual Machine, so Groovy scripts can use these types directly.

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. You write DQL as strings, called query strings, that can contain a mixture of Java and Groovy code. Query strings give you access to Deephaven's built-in functions. You typically use query strings in table operations, such as creating columns or filtering rows. Here's a simple DQL example:

i and ii are special variables that hold each row's index, as an int and a long respectively.

DQL provides four tools for working with 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. You can call these functions directly in query strings and in Groovy scripts. The following example uses the built-in Deephaven function now to get the current time as a Java Instant:

You can also apply these functions 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:

In addition to functions, Deephaven offers many built-in constants for expressions involving time values. Many built-in functions work in nanoseconds, and these constants convert between nanoseconds and larger units:

• MICRO, MILLI, SECOND, MINUTE, HOUR, DAY, WEEK, and YEAR_365 each equal the number of nanoseconds in that unit.
• YEAR_AVG equals the number of nanoseconds in an average Gregorian year.
• Reciprocal constants such as SECONDS_PER_NANO convert nanoseconds to larger units.

The following example divides a nanosecond count by several of these constants:

2. Java object methods

Java is an object-oriented programming language, so every Java object has methods. You can call those methods in query strings. This example extends the epochDaysToLocalDate example from Built-in Java functions, which turns a count of days since the Unix epoch into a LocalDate. It uses the getDayOfWeek method of each LocalDate object to extract the day of the week:

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 comparison operators

Query strings support arithmetic operators such as +, -, and *, and comparison operators such as <, <=, >, >=, and ==, for Java time types. For instance, you can add a Period or a Duration to an Instant, or multiply a Duration or a Period by an integer. This example uses the built-in parsePeriod and parseDuration functions to create period and duration columns from strings. The overloaded + operator then adds them to the Timestamp column, and the overloaded * operator creates a column of timestamps that increment daily:

An Instant has no time zone. You can add a Period to an Instant only when the period is in days or weeks. Each of those days counts as exactly 24 hours, as it does in a Duration. To add months or years, or days that follow daylight-saving changes, add the period to a ZonedDateTime instead.

The + operator in a query string calls the built-in plus function, so you rarely need to call plus directly. The following query compares the two. It also uses + to add a negative period literal and a built-in constant.

The above example creates a source table from an Instant called baseTime. Each row of the source table is one second after the previous. The example then creates a one-hour duration and a one-day period, and derives five tables from source:

• result1 uses the query language's built-in plus function to add hourDuration to Timestamp, so the TS2 column contains timestamps one hour after those in the Timestamp column.
• result2 uses the same built-in plus function, but adds dayPeriod 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 contains timestamps one day before those in the Timestamp column.
• result5 uses the overloaded + operator to add the MINUTE constant, which is a number of nanoseconds, to Timestamp. Thus, TS2 contains timestamps one minute after those in the Timestamp column.

4. Date-time literals

You can write date-time values as literals directly in query strings. The same text, written as a backtick string, can be passed to built-in parse functions such as parseDuration.

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

Using query string time literals can yield more compact and more efficient code. In the prior example, the engine evaluates parseDuration(`PT24h`) once per row but evaluates the literal 'PT24h' once for the whole table. On large tables, the literal is much faster:

You can create most of the seven key Java types with literals or with functions like parseDuration:

Duration and period strings both start with P. A duration uses T to separate its day part from its time part, as in PnDTnHnMn.nS. A period has no time part: it uses the format PnYnMnWnD, where you can leave out any unit you don't need, as in P2Y3M or P4W.

Durations use the following letters for different units of time:

• D: Day (always 24 hours), before the T
• H: Hour
• M: Minute
• S: Second

Periods use the following letters for different units of time:

• Y: Year
• M: Month
• W: Week
• D: Day

A - sign can come before the whole string, which negates every component, or before any single component. For example, a duration of PT1H1M-1S means one second short of one hour and one minute. Similarly, a period of P1Y1M-1D means one day short of one year and one month.

Put it all together

To illustrate the power and ease of working with natively supported date-times, this example combines time literals, overloaded arithmetic operators, and Java time methods to create timestamps, compute time differences, and extract dates and days of the week in the Tokyo and New York time zones. ET is Deephaven's built-in alias for America/New_York. For more about aliases, see Time zones and aliases.

Hopefully, it's apparent that working with date-times in Deephaven queries is a breeze. The built-in time functions cover most date-time operations in query strings.

Filter by time

This section filters an events table by time. It uses the date-time literals and comparison operators introduced in The Deephaven Query Language.

The following code creates an events table with four rows. All times use the ET (US Eastern) time zone.

Filter to events after a certain time

The following query returns all events after 10 AM on 2020-04-01. Query strings support comparison operators on date-times. To compare a date-time column with a fixed date-time, write the date-time as a literal in single quotes (').

Filter to events within a range

The following example returns all events between 10 AM and 4 PM on 2020-04-01 by combining two comparisons with && in one query string.

The following example returns the same events with the inRange function.

Date-times in Groovy scripts

Groovy runs on the Java Virtual Machine, so Groovy scripts use the same key Java types as query strings, with no conversion between them. You can call built-in functions such as now, parseInstant, and plus in scripts as well as in query strings. For example, plus(parseInstant("2020-04-01T12:00:00 ET"), parsePeriod("P1D")) in a script adds a one-day period the same way result2 does in Arithmetic and comparison operators.

Current time and the Deephaven clock

To see the current date and time, run the following:

now and today return current date-time information according to the Deephaven clock. By default, the Deephaven clock is the system clock of the computer running Deephaven. DateTimeUtils.setClock replaces it with a custom clock, such as a simulated clock. When that happens, now and today return times from that clock instead of the system clock. Replaying historical data does not change the Deephaven clock: the replayer drives its replayed tables from its own simulated clock.

Differences between instants

An Instant represents a point in time with no time zone attached. In table columns, Deephaven stores each Instant as a signed 64-bit count of nanoseconds since the Unix epoch (January 1, 1970, 00:00:00 UTC). You can create Instant values in a script, add durations and periods to them, and subtract one Instant from another, both in scripts and in query strings.

The following example creates two Instant values, time1 and time2, exactly one 365-day year apart. diffNanos returns their difference, 31,536,000,000,000,000 nanoseconds, because a 365-day year has 31,536,000 seconds.

How instants print

Printing an Instant, for example with println now(), produces an ISO-8601 string in UTC, such as 2021-09-09T15:58:41.041Z. The format consists of the following fields:

yyyy-MM-ddTHH:mm:ss[.fff]Z

• yyyy: the year
• MM: the month
• dd: the day
• T: the separator between the date and time
• HH: the hour of the day, from 00 to 23
• mm: the minute of the hour
• ss: the second of the minute
• [.fff]: the optional fraction of a second, printed with 3, 6, or 9 digits as needed, or omitted when it is zero
• Z: the time zone indicator for UTC

Instants always print in UTC. To show a value in a specific time zone, use formatDateTime.

Time zones and aliases

Deephaven uses Java's ZoneId class to store time zone information. In addition to standard zone IDs such as UTC or Z for Coordinated Universal Time, Deephaven supports time zone aliases such as ET for US Eastern time. The timeZone function returns the ZoneId for a zone ID or alias:

By default, Deephaven uses the JVM's default time zone, which is UTC in the Deephaven Docker images. For example, writeCsv writes Instant columns in this time zone unless you pass it a different one. To change the default, see How to set the timezone.

The following example parses the same clock time in the ET (US Eastern) and MT (US Mountain) time zones. Both values print in UTC, ending with Z, so the output shows the two-hour difference between the time zones.

Related documentation

• How to use filters
• How to work with strings
• date-time
• Date-time literals in query strings
• Business calendars
• update
• where