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 Python scripts.

This guide covers:

• The Java date-time types the engine supports natively.
• The query-language tools for working with those types.
• The common Python date-time packages.
• The deephaven.time module, which reads the Deephaven clock, manages time zones, creates formatters for parsing date-time strings in table columns, and converts between Python and Java types.
• How to keep those conversions from slowing queries down.

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, but most people use Deephaven through the Python API. The Deephaven Query Language lets Python code create and manipulate these Java types.

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 Python code. Query strings give you access to Deephaven's built-in functions and let Python and Java code work together. You typically use query strings in table operations, such as creating columns or filtering rows. Here's a simple DQL example:

In this example, the query engine converts these simple query strings to Java, compiles them, and executes them. When query strings include Python functions, the query engine uses both Java and Python to evaluate the expressions. 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. DQL supports calling these functions directly in query strings, which makes all of Deephaven's built-in Java functions available from the Python API. 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.

DQL lets you call both the built-in functions and the methods of Java objects from the Python API.

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.

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.

Date-times in Python

Like Java, Python has its own ecosystem of types for representing dates, times, and durations. Python's standard library has its own date-time types in the datetime package, but unlike Java, where libraries share the java.time types, popular Python packages such as numpy and pandas define their own date-time types. Each package that implements its own date-time types also provides functions and methods for working with those types. This means that in Python, there are often many ways to solve a date-time problem.

Three primary Python packages support date-time types and operations: datetime, numpy, and pandas.

The datetime package

The datetime package is a part of the Python Standard Library, so it comes with every Python installation. The package provides several types to represent various date-time quantities:

Many types in the datetime package can carry a time zone. An instance of a type with a time zone is called aware, while instances of types with no time zone information are called naive.

The API is simple:

It's easy to convert to different time zones:

Durations result from date-time arithmetic:

You can also construct durations directly:

The datetime API is simple to use. However, datetime has no array operations, so you often need loops or list comprehensions to operate on arrays of date-time objects, which can slow code considerably.

The numpy package

numpy is the leading implementation of arrays and array operations in Python. It's not a part of the Standard Library. numpy supports many different array types, including date-time types. It has only two date-time types:

Unlike the datetime package, numpy does not support any kind of time zone awareness. In the language of the datetime package, all of the numpy types are naive. You can add time zone offsets to any numpy date-time value manually, but that gets cumbersome.

Since numpy is built around arrays, many of its methods for creating and manipulating arrays work on date-time types:

The array-first model lets you operate on all array elements at once:

numpy array operations are fast. However, its API covers few common time-based operations, and it has no time zone support.

The pandas package

pandas is a popular Python package among data scientists, machine learning experts, and more. It provides substantial date-time support and combines the strengths of datetime and numpy: time zone awareness, a rich API, and fast array operations.

pandas uses different types to represent scalar and array versions of each type:

The date-time types also support imposing a time zone, but unlike the datetime package, pandas provides no explicit time zone type.

In pandas, you can create single date-times and arrays of date-times:

Methods such as tz_localize impose a time zone on an entire array:

And there are helper attributes and methods for many common date-time operations, all with array support:

Overall, pandas offers the richest API with array support. It's more complex than datetime or numpy, but can solve a wider range of problems than both of the alternatives.

deephaven.time

The deephaven.time Python module reads the Deephaven clock, manages time zone aliases, creates Java date formatters for parsing date-time strings, and converts between Python's common date-time types and their Java equivalents. Use its conversion functions to pass Python date-time values into DQL query strings and to bring Java date-time values back into Python.

Current time and the Deephaven clock

The deephaven.time module includes two functions, dh_now and dh_today, that return the current date and time 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, dh_now and dh_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.

Here is a simple example of dh_now and dh_today:

Time zones and aliases

Keeping track of time zones is an important task when working with date-time data. deephaven.time offers three functions to facilitate working with time zones in Deephaven:

• dh_time_zone
• time_zone_alias_add
• time_zone_alias_rm

To get the current time zone of the running Deephaven engine, use dh_time_zone:

The Deephaven engine uses the JVM's default time zone, which is Coordinated Universal Time (UTC) in the Deephaven Docker images. To change it, see How to set the timezone. To change the time zone the UI displays without changing the engine time zone, see How to set date-time format.

Deephaven can make use of any valid Java time zone within queries. However, full Java time zone names like America/Chicago can be cumbersome to type out repeatedly. To make query strings shorter, Deephaven supports common time zone aliases that you can use in place of the full Java names:

When the default aliases aren't enough, use time_zone_alias_add to create a custom alias for any time zone, and time_zone_alias_rm to remove it. This example creates an alias for Indian Standard Time and then removes it:

Parse and format date-time columns

Date-time values in real datasets often use formats that read_csv doesn't recognize as date-times. Take this example:

The BEGIN and END columns are dates, formatted as yyyyMMdd. However, read_csv doesn't recognize that format as a date-time format, so it reads the columns as integers:

As integers, these columns can't use Deephaven's built-in date-time functions. To parse them, use the simple_date_format function from deephaven.time.

To use simple_date_format, pass a string that describes the format of the input date-time values. The BEGIN and END columns use the format yyyyMMdd, so pass "yyyyMMdd".

Use lowercase y and d in the pattern. In Java date-time patterns, uppercase Y means week-year and uppercase D means day-of-year.

For example, this query parses BEGIN and END straight to LocalDate with a thread-safe formatter:

For a small table, create a separate simple_date_format formatter for each column:

input_format and input_format2 parse the BEGIN and END columns in a query. Their parse method takes a string, so convert each int value to a string first. Then call toInstant on the result to get a Java Instant:

Deephaven's built-in functions convert the resulting columns to other Java date-time types. This example converts BEGIN and END to LocalDate columns using toLocalDate:

To verify that these columns have the correct type, check the metadata table:

After the columns hold a Java date-time type, use format_columns to change how the UI displays them. Formatting changes only the display, not the stored values. A Date(...) format in format_columns applies only to Instant and ZonedDateTime columns, so convert other types, such as LocalDate, to one of those first. The format string must be a valid Java date-time format.

Here's an example that converts the LocalDate columns from above to Instant columns, and reformats them using format_columns:

Python to Java conversions

The to_j_* functions in deephaven.time convert scalar date-time values from datetime, numpy, and pandas to the seven key Java types. They don't accept array types such as pandas.DatetimeIndex and pandas.TimedeltaIndex. They also don't accept pandas.Period or pandas.PeriodIndex. The conversion summary maps each Python type to its Java equivalent.

The Python-to-Java conversion functions in deephaven.time are:

• to_j_time_zone
• to_j_local_date
• to_j_local_time
• to_j_instant
• to_j_zdt
• to_j_duration
• to_j_period

Here's an example using to_j_instant:

The metadata table shows that all three columns are Instant columns. to_j_instant converts values from datetime, numpy, and pandas to a Java type that the query engine uses directly.

The following example converts the earliest and latest timestamps in a pandas DataFrame to Java and uses them to filter a Deephaven table:

Java to Python conversions

In addition to providing functions to convert Python types to their equivalent Java types, deephaven.time provides a suite of functions to convert Java types to Python types. These functions convert the Java types LocalDate, LocalTime, Instant, ZonedDateTime, Duration, and Period to their Python equivalents. No function converts a Java ZoneId to Python. The conversion summary lists the functions for the most common pairs of types.

The Java-to-Python conversion functions in deephaven.time are:

• to_date
• to_time
• to_datetime
• to_timedelta
• to_np_datetime64
• to_np_timedelta64
• to_pd_timestamp
• to_pd_timedelta

Not every Java Period converts. to_timedelta and to_pd_timedelta accept only day- or week-based periods, and to_np_timedelta64 accepts a period in a single unit of days, months, or years.

These functions are useful when a Python function called from a query string needs a Python date-time value, for example to pass it to a Python library that has no Java equivalent. Each such call crosses the Python-Java boundary at least once per row, plus once more for each conversion inside the function, so reserve this pattern for work that has no built-in Java function. See Performance and the Python-Java boundary.

Here's an example that trains a simple ARIMA model on a toy dataset and uses a Python function to make forecasts with that model in a Deephaven table:

The following code evaluates the mean squared error of the model's predictions on the test data:

Conversion summary

The following table lists each Python date-time type, its Java equivalent, and the deephaven.time functions that convert between them. NA means that no conversion function exists.

Performance and the Python-Java boundary

Because Deephaven queries can contain Python and the Deephaven query engine is implemented in Java, performance-conscious users should be aware of Python-Java boundary crossings. Every time a query's execution passes between Python and Java, there is a performance penalty. To keep queries fast, minimize boundary crossings.

Here is an example that demonstrates the effects that excess boundary crossings can have on performance:

The resulting tables are identical, but their execution times differ enormously:

Careful inspection of the slower code reveals many boundary crossings. If the table has n rows, there are:

1. n boundary crossings to call the Python function.
2. n boundary crossings to convert the Java Instant to a Python datetime.
3. n boundary crossings to convert a Python datetime to a Java Instant.

Thus, in total, there are 3n boundary crossings, which significantly slows the query. To use a Python date-time value in a query, convert it to Java once, outside the query string, and then use Java arithmetic in the query string. The j_min and j_max values in Python to Java conversions follow this pattern.

The next example looks up time values by index in a query in three ways. Each way crosses the boundary a different number of times, and the execution times show how those small per-crossing overheads add up.

Related documentation

• How to use filters
• How to work with strings
• date-time
• Date-time literals in query strings
• Business calendars
• update
• where
• The Python-Java boundary
• deephaven.time Pydoc