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, time_literals_types, shows each column's type:

Note

Date-time literals use single quotes. Strings in query strings use backticks.

Create tables with timestamps

new_table

new_table and datetime_col create a table with an Instant column from Python date-time objects:

empty_table

To use the query language instead of Python to create a timestamped table, use empty_table and update:

time_table

time_table 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" or "PT00:00:00.001".
  • An integer number of nanoseconds.
  • A Python time duration type, such as datetime.timedelta.

The optional start_time argument sets the start time. 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:

The t_time_table table ticking in the Deephaven IDE, adding one row each second

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, describe their format to simple_date_format from the deephaven.time module, which returns a Java SimpleDateFormat:

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 with_serial, so the engine never runs the formula concurrently with itself. The later examples on this page that use simple_date_format 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:

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, describe that format to simple_date_format, which returns a Java SimpleDateFormat. Parsing gives an Instant, not a date, so the examples convert it back:

Here, the date is stored as an integer, so the example converts it to a String with String.valueOf before parsing it with a format from simple_date_format:

Parse times

Much like dates, ISO-formatted time strings convert to LocalTime with parseLocalTime:

When the column uses a non-ISO format, describe that format to simple_date_format. As with dates, parsing gives an Instant, not a time, so the examples convert it back:

Times with no delimiters may be stored as integers. Convert them to strings first:

Format date-time display

The format_columns 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:

format_columns also works on ticking tables. This example formats a Now column, computed with now, in a time_table to show nanosecond precision:

Date-time settings in the UI

The Settings menu in the Deephaven IDE customizes how date-time values appear:

The Deephaven IDE's Settings menu

  • 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

In Python code, use dh_now, dh_today, and dh_time_zone:

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:

time_zone_alias_add adds a custom time zone alias, which queries can then use. time_zone_alias_rm 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:

This example bins Timestamp into 10-minute intervals with a duration:

Downsampling reduces a table to fewer rows, such as one row per bin. first_by, last_by, and avg_by downsample by grouping on the bin column:

To perform multiple aggregations at once on binned timestamps, use agg_by with the agg suite. This example computes the open, high, low, and close of X in each 10-minute bin with agg.first, agg.max_, agg.min_, and agg.last:

Time-based filtering

Deephaven's filter methods and comparison operators work with timestamps. The last example combines a timestamp comparison with where_one_of, 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:

ConstantTypeDescription
DAYlongOne day in nanoseconds.
DAYS_PER_NANOdoubleNumber of days per nanosecond.
HOURlongOne hour in nanoseconds.
HOURS_PER_NANOdoubleNumber of hours per nanosecond.
MICROlongOne microsecond in nanoseconds.
MILLIlongOne millisecond in nanoseconds.
MINUTElongOne minute in nanoseconds.
MINUTES_PER_NANOdoubleNumber of minutes per nanosecond.
SECONDlongOne second in nanoseconds.
SECONDS_PER_NANOdoubleNumber of seconds per nanosecond.
WEEKlongOne week in nanoseconds.
YEAR_365longOne 365-day year in nanoseconds.
YEAR_AVGlongOne average (365.2425-day) year in nanoseconds.
YEARS_PER_NANO_365doubleNumber of 365-day years per nanosecond.
YEARS_PER_NANO_AVGdoubleNumber of average (365.2425-day) years per nanosecond.
ZERO_LENGTH_INSTANT_ARRAYInstant[]A zero-length array of instants.

These constants are available in query strings. In Python code, use datetime.timedelta or numpy types instead.

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 in on. 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 t_noisy from the aj (as-of join) section:

Like aj, raj can match key columns exactly before the timestamp column. This example reuses t_group and t_noisy_group from the aj (as-of join) section:

Work with Python time types

Python code commonly uses date-time types from the standard datetime module and the numpy and pandas libraries. Deephaven provides interoperability with these types through the deephaven.time module.

Python to Java

The deephaven.time module provides the following functions for converting datetime, numpy, and pandas types to Java types for use in query strings:

Warning

For performance, do not call these functions inside query strings. Call them outside the query string to create objects, then use those objects in the query string.

This example demonstrates the proper use of these functions:

These functions each accept several argument types, such as strings and the matching datetime, numpy, or pandas types. The deephaven.time Pydoc lists the types each function accepts.

Java to Python

The deephaven.time module also provides the following functions for converting Java types to their Python equivalents:

A common use is inside a Python function that a query string calls with date-time arguments:

Note

For this example, the built-in diffSeconds function is the better choice. Converting to Python types is useful when the query language has no function for the task.