Time operations cheat sheet
Deephaven is a real-time data platform, and many Deephaven applications use time-stamped data in some way. This cheat sheet outlines the tools available for working with that data. The guide on working with time in Deephaven provides conceptual detail that this cheat sheet does not cover.
Date-time literals
In query strings, a value of more than one character in single quotes is a date-time literal. The engine parses it as an instant, a date, a time, a time zone, a duration, or a period. Date-time literals in query strings lists the formats for each type. Short zone names such as ET and CT are Deephaven time zone aliases. The following example creates one column of each kind. Its meta table, timeLiteralsTypes, shows each column's type:
Note
Date-time literals use single quotes. Strings in query strings use backticks.
Create tables with timestamps
newTable
newTable and instantCol create a table with an Instant column. This example builds the values with parseInstant:
emptyTable
To create a timestamped table with the query language, use emptyTable and update:
timeTable
timeTable creates a ticking table, which adds a new row at a fixed interval. It takes the interval in any of these forms:
- A duration string, such as
PT1S,PT1H30M, orPT00:00:00.001. - A number of nanoseconds, such as
5 * SECOND, which uses a built-in constant.
An optional first argument sets the start time, as an Instant or as a date-time string in the format parseInstant accepts. When the start time is in the past, the table immediately contains one row for each interval between the start time and the current time:
Parse time-stamped data
Each table example in this section converts a raw table and also shows the meta table of both the raw and the converted table, so the column types can be compared before and after the conversion.
Parse date-times
parseInstant converts strings in an ISO 8601-style format to the Instant date-time type. The format is yyyy-MM-ddTHH:mm:ss[.SSSSSSSSS] TZ, where TZ is a time zone name or alias:
parseInstant accepts only ISO 8601-style date-times and plain integer offsets from the Unix epoch, so it cannot parse custom layouts such as 01/01/2023 12:30:01 CT. To parse them, use a Java SimpleDateFormat that describes their format:
Warning
A SimpleDateFormat is not thread-safe. On a large table, the engine can run a formula on several threads at once, and a shared SimpleDateFormat can then return wrong values. The example above prevents this. It passes the formula to Selectable.parse and marks the column serial with withSerial, so the engine never runs the formula concurrently with itself. The later examples on this page that use SimpleDateFormat need the same change on large tables.
In this example, the strings use a different layout and already carry the abbreviation CST. SimpleDateFormat recognizes CST, so the query does not call replaceAll:
Here, the dates and times are stored in separate columns. Extract the data from each column and use parseInstant or toInstant to get the final result:
Java methods can also build the arguments that toInstant takes. Here, LocalDate.of creates a LocalDate, and the example passes it to toInstant:
Integers can hold an offset from the Unix epoch. epochSecondsToInstant converts a count of seconds to an Instant. epochAutoToInstant infers whether the offset is in seconds, milliseconds, microseconds, or nanoseconds from its magnitude:
parseInstant and the epoch conversion functions also work in Groovy code outside of query strings:
Parse dates
When a date column holds strings in ISO 8601 format, such as 2023-01-01, parseLocalDate converts the column to a LocalDate:
When the column uses a non-ISO format, create a SimpleDateFormat that describes that format. Parsing gives an Instant, not a date, so the examples convert it back:
- The
SimpleDateFormatparses a string with no time zone in the engine time zone. timeZonereturns that time zone.toLocalDate, given the parsedInstantand the result oftimeZone, returns the original date.
Here, the date is stored as an integer, so the example converts it to a String with String.valueOf before parsing it with SimpleDateFormat:
Parse times
Much like dates, ISO-formatted time strings convert to LocalTime with parseLocalTime:
When the column uses a non-ISO format, create a SimpleDateFormat that describes that format. As with dates, parsing gives an Instant, not a time, so the examples convert it back:
- The
SimpleDateFormatparses a string with no time zone in the engine time zone. timeZonereturns that time zone.toLocalTime, given the parsedInstantand the result oftimeZone, returns the original time.
Times with no delimiters may be stored as integers. Convert them to strings first:
Format date-time display
The formatColumns table operation changes how a date-time column is displayed. It does not change the underlying data. Reformat timestamps using valid Java date-time formats. The D (day of year) pattern is not supported:
formatColumns also works on ticking tables. This example formats a Now column, computed with now, in a timeTable to show nanosecond precision:
Date-time settings in the UI
The Settings menu in the Deephaven IDE customizes how date-time values appear:

- Time zone sets the default time zone in which the UI displays date-time values in every table.
- DateTime sets the default format for date-time values.
- Format by Column Name & Type adds rules that set the display format for date-time columns with a given name in every table.
To change the format of one column in one table, use its column header menu. See Set date-time format for details.
Get current time information
In a query
The now, today, and timeZone functions return the current time, the current date as a yyyy-MM-dd string, and the engine time zone. Use todayLocalDate to get the current date as a LocalDate:
Outside of a query
The now, today, and timeZone functions also work in Groovy code outside query strings:
Time zones
The engine time zone is the JVM's default time zone, which is set when the server starts. A SimpleDateFormat uses it to parse strings that have no time zone, and timeZone returns it. It is not always the same as the time zone the UI displays, which the Date-time settings in the UI section covers.
Change engine time zone
To change the engine time zone, set the user.timezone JVM property at startup, as described in Set the default time zone.
Time zone aliases
Deephaven provides time zone aliases for some common time zones. The default aliases are ET, CT, MT, PT, HK, JP, and LON:
timeZoneAliasAdd adds a custom time zone alias, which queries can then use. timeZoneAliasRm removes it:
Manipulate timestamps
The examples in this section use the following table:
Convenience methods for date-times
Date-time columns carry a lot of information. Deephaven's auto-imported functions extract it:
Bin timestamps
The upperBin and lowerBin functions group timestamps into bins at regular intervals. Each function accepts the bin interval in either of these forms:
- A number of nanoseconds, such as
5 * SECOND, which uses a built-in constant. - A duration, such as
'PT5S'.
This example bins Timestamp into 10-minute intervals with a duration:
Downsampling reduces a table to fewer rows, such as one row per bin. firstBy, lastBy, and avgBy downsample by grouping on the bin column:
To perform multiple aggregations at once on binned timestamps, use aggBy. This example computes the open, high, low, and close of X in each 10-minute bin with AggFirst, AggMax, AggMin, and AggLast:
Time-based filtering
Deephaven's filter methods and comparison operators work with timestamps. The last example combines a timestamp comparison with Filter.or, which keeps rows that match any of its filters:
Math with date-times
Arithmetic operators work on date-time values. Adding or subtracting a duration shifts an Instant, and so does a period of days or weeks. A period with months or years needs a ZonedDateTime. Subtracting one Instant from another gives the difference in nanoseconds. Dividing by a built-in constant such as DAY converts nanoseconds to another unit:
Date-time arithmetic also works directly in filters, without new columns:
Built-in constants
Deephaven provides built-in constants, most of them for converting between time units. The Math with date-times examples use YEAR_365 and DAY. The following table lists the available constants:
| Constant | Type | Description |
|---|---|---|
DAY | long | One day in nanoseconds. |
DAYS_PER_NANO | double | Number of days per nanosecond. |
HOUR | long | One hour in nanoseconds. |
HOURS_PER_NANO | double | Number of hours per nanosecond. |
MICRO | long | One microsecond in nanoseconds. |
MILLI | long | One millisecond in nanoseconds. |
MINUTE | long | One minute in nanoseconds. |
MINUTES_PER_NANO | double | Number of minutes per nanosecond. |
SECOND | long | One second in nanoseconds. |
SECONDS_PER_NANO | double | Number of seconds per nanosecond. |
WEEK | long | One week in nanoseconds. |
YEAR_365 | long | One 365-day year in nanoseconds. |
YEAR_AVG | long | One average (365.2425-day) year in nanoseconds. |
YEARS_PER_NANO_365 | double | Number of 365-day years per nanosecond. |
YEARS_PER_NANO_AVG | double | Number of average (365.2425-day) years per nanosecond. |
ZERO_LENGTH_INSTANT_ARRAY | Instant[] | A zero-length array of instants. |
The constants are also available in Groovy code outside of query strings:
Time-based joins
Timestamps from different datasets rarely match exactly, so exact-match joins on timestamps find few matches. The aj and raj joins match on the closest timestamp instead. See Inexact, time-series, and range joins for more.
The examples in this section join the table t from Manipulate timestamps, which has one row per minute and a random X column, to a table whose timestamps are offset by random amounts.
aj (as-of join)
The as-of join aj joins tables on timestamp columns by choosing values from the right key column that are as close as possible to values in the left key column without going over:
aj can also match one or more key columns exactly before the as-of match. List the timestamp column last among the match columns. This example matches Group exactly:
raj (reverse as-of join)
The reverse as-of join raj joins tables on timestamp columns by choosing values from the right key column that are as close as possible to values in the left key column without going under. This example uses tNoisy from the aj (as-of join) section:
Like aj, raj can match key columns exactly before the timestamp column. This example reuses tGroup and tNoisyGroup from the aj (as-of join) section:
Related documentation
- Work with time
- Parallelizing queries
- Date-time literals in query strings
- Create a time table
- Formulas
- Filter table data
- Operators
- Downsampling
- Combined aggregations
- Exact and relational joins
- Inexact, time-series, and range joins
- Set date-time format
- Set the default time zone
- Date-time types
- Durations
- Periods
- Auto-imported functions
AggFirstAggLastAggMaxAggMinaggByajatMidnightavgBydayOfMonthdayOfWeekdayOfYearemptyTableepochAutoToInstantepochSecondsepochSecondsToInstantfirstByformatColumnshourOfDayinstantColintCollastBylongCollowerBinmetaminuteOfDaymonthOfYearnewTablenowparseInstantparseLocalDateparseLocalTimeparseTimeZonerajselectstringColtimeTabletimeZonetimeZoneAliasAddtimeZoneAliasRmtodaytoInstanttoLocalDatetoLocalTimeupdateupperBinwhereyearDateTimeUtilsJavadocFilterJavadoc