---
title: Working with time
---

> [!WARNING]
> Legacy documentation: This documentation applies to **Legacy Deephaven Enterprise only** and does not apply to Core+.

> [!NOTE]
> For Core+ workers, see Community Core documentation for [time](/core/docs/conceptual/time-in-deephaven).

The ability to filter data based on temporal data is fundamental for users working with time-series data, and Deephaven offers many methods and utilities to do so. Before discussing those methods, we need to understand the specific syntax used for temporal units.

## Temporal Units

Three distinctive temporal units are available in the query language:

### DateTime

A DateTime is a specific moment in time, including units as large as the year and as small as the nanosecond. The format of a DateTime follows:

`yyyy-mm-ddThh:mm:ss.millis/micros/nanos TZ`

> [!NOTE]
> The `T` between `dd` and `hh` is a divider, and `TZ` represents the time zone.

For example:

- `2019-10-21T16:33 UTC` - October 25, 2019 at 4:33 pm in UTC

- `2017-05-21T14:45:52.517863000 NY` - May 21, 2017 at 2:45:52.517863000 pm in New York

- `2018-03-08T09:12 JP` - March 8, 2018 at 9:12 am in Tokyo

Using the full DateTime string allows for very fine granularity and specificity in selecting values. However, seconds and smaller values are optional and do not need to be specified in the Deephaven Query language.

### Period

A period is a duration of time and can be represented with the following patterns: `-#Y#M#W#DT#H#M#S`. The negative sign and partial periods are optional. For example, if you wanted to compute the `DBDateTime` 1 day and 1 hour from another `DBDateTime`, you could use `time+'1dT1h'`.

### Durations/Times (part of a `DBDateTime`)

Durations/Times can be represented with the following patterns: `-hh:mm:ss.millis/micros/nanos`. The negative is optional. This gets converted into a Long representing the nanoseconds since January 1, 1970 UTC. For example, `'01:02:03.456'` is 1 hour, 2 minutes, 3 seconds, and 456 milliseconds. Durations/Times can be added to DBDateTimes.

## DateTime Methods

Methods in the `DBTimeUtils` class can be used to create, manipulate, and gather information about date-times, time/durations, and periods. All of the functions handle null values.

These methods are automatically imported by default when using Groovy as your scripting language. When using Python, you'll need to import the `DBTimeUtils` class:

```python
from deephaven import *
print dbtu.HOUR
```

| Method                               | Description                                                                                                                              |
| ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------- |
| `convertDateTime(string)`            | Returns a String as a `DBDateTime` (throws an exception if invalid).                                                                     |
| `convertTime(string)`                | Returns a String as a time/duration (throws an exception if invalid).                                                                    |
| `convertPeriod(string)`              | Returns a String as a period (throws an exception if invalid).                                                                           |
| `convertDateTimeQuiet(string)`       | Returns a String as a `DBDateTime` (returns null if invalid).                                                                            |
| `convertTimeQuiet(string)`           | Returns a String as a time/duration (returns null if invalid).                                                                           |
| `convertPeriodQuiet(string)`         | Returns a String as a period (returns null if invalid).                                                                                  |
| `currentTime()`                      | Returns a `DBDateTime` representing the current time.                                                                                    |
| `dateAtMidnight(dateTime, timeZone)` | Returns a `DBDateTime` representing that date at midnight in the specified time zone.                                                    |
| `dayOfMonth(dateTime, timeZone)`     | Returns the day of the month of the specified `DBDateTime` and time zone.                                                                |
| `dayOfWeek(dateTime, timeZone)`      | Returns the day of the week of the specified `DBDateTime` and time zone (1 = Monday, 7 = Sunday).                                        |
| `dayOfYear(dateTime, timeZone)`      | Returns the day of the year (Julian date) of the specified `DBDateTime` and time zone.                                                   |
| `diff(dateTime1, dateTime2)`         | Returns the difference in nanos from `dateTime1` to `dateTime2` (Note: you can use `dateTime2-dateTime1` in the query language).         |
| `format(nanos)`                      | Returns a formatted String for this `DBDateTime` passed in as nanos.                                                                     |
| `format(dateTime, timeZone)`         | Returns a formatted String (date and time) for a `DBDateTime`.                                                                           |
| `formatDate(dateTime, timeZone)`     | Returns a formatted String (date only) for a `DBDateTime`.                                                                               |
| `hourOfDay(dateTime, timeZone)`      | Returns the hour of the day of the specified `DBDateTime` and time zone.                                                                 |
| `isAfter(dateTime1, dateTime2)`      | Returns a Boolean value depending on whether `dateTime1>dateTime2` (Note: you can just use `dateTime1>dateTime2` in the query language). |
| `isBefore(dateTime1, dateTime2)`     | Returns a Boolean value depending on whether `dateTime1<dateTime2` (Note: you can just use `dateTime1<dateTime2` in the query language). |
| `lowerBin(dateTime, intervalNanos)`  | Returns the lower bin; bins the `DBDateTime`s according to the `intervalNanos` .                                                         |
| `millis(dateTime)`                   | Retruns the `DBDateTime` of the specified `dateTime` in milliseconds from epoch.                                                         |
| `millisOfDay(dateTime, timeZone)`    | Returns the milliseconds since midnight of the specified `DBDateTime` and time zone.                                                     |
| `millisOfSecond(dateTime, timeZone)` | Returns the milliseconds since the start of the second of the specified `DBDateTime` and time zone.                                      |
| `millisToNanos(millis)`              | Returns the nanosecond equivalent of the specified milliseconds.                                                                         |
| `millisToTime(millis)`               | Returns the milliseconds from epoch as a `DBDateTime`.                                                                                   |
| `minus(d1, nanos)`                   | Returns the difference of a `DBDateTime` and a time/duration (Note: you can use `dateTime-nanos` in the query language).                 |
| `minus(dateTime1, dateTime2)`        | Returns the difference in nanos between two `DBDateTime`s (Note: you can use `dateTime1-dateTime2` in the query language).               |
| `minus(dateTime, period)`            | Returns the difference of a `DBDateTime` and a period (Note: you can use `dateTime-period` in the query language).                       |
| `minuteOfDay(dateTime, timeZone)`    | Returns the minutes since midnight of the specified `DBDateTime` and time zone.                                                          |
| `minuteOfHour(dateTime, timeZone)`   | Returns the minutes since the top of the hour of the specified `DBDateTime` and time zone.                                               |
| `monthOfYear(dateTime, timeZone)`    | Returns the month of the year of the specified `DBDateTime` and time zone (January = 1).                                                 |
| `nanos(dateTime)`                    | Returns the `DBDateTime` of the specified `dateTime` in nanoseconds from epoch.                                                          |
| `nanosOfDay(dateTime, timeZone)`     | Returns the nanoseconds since midnight of the specified `DBDateTime` and time zone.                                                      |
| `nanosOfSecond(dateTime, timeZone)`  | Returns the nanos since midnight of the specified `DBDateTime` and time zone.                                                            |
| `nanosToMillis(nanos)`               | Returns the millisecond equivalent of the specified nanoseconds.                                                                         |
| `nanosToTime(nanos)`                 | Returns the nanoseconds from epoch as a `DBDateTime`.                                                                                    |
| `plus(dateTime, nanos)`              | Returns the sum of a `DBDateTime` and a time/duration (Note: you can use `dateTime+nanos` in the query language).                        |
| `plus(dateTime, period)`             | Returns the sum of a `DBDateTime` and a period (Note: you can use `dateTime+period` in the query language).                              |
| `secondOfDay(dateTime, timeZone)`    | Returns the seconds since midnight of the specified `DBDateTime` and time zone.                                                          |
| `secondOfMinute(dateTime, timeZone)` | Returns the seconds since the top of the minute of the specified `DBDateTime` and time zone.                                             |
| `upperBin(dateTime, intervalNanos)`  | Returns the uppder bin; bins the `DBDateTime`s according to the `intervalNanos`.                                                         |
| `year(dateTime, timeZone)`           | Returns the year of the specified `DBDateTime` and time zone.                                                                            |
| `yearOfCentury(dateTime, timeZone)`  | Returns the year of the century of the specified date time and time zone.                                                                |

## Downsampling Temporal Data

### Binning Intervals

Downsampling time series data can be done by calculating the bin intervals for values and then using `firstBy` or `lastBy` to select the first or last row for each bin.

For example, using a table named "x" that contains a `DBDateTime` column called Timestamp, you can get five-minute interval samples from the data using the following:

`downsampledx = x.updateView("TimeBin=upperBin(Timestamp, 5 * MINUTE)").lastBy("TimeBin").dropColumns("TimeBin")`

This is a little tedious in that you have to add the TimeBin column to get the `lastBy`, and then you drop it because you don't want it as part of your result set. An alternate way to do this is to use [DownsampledWhereFilter](./filter.md#downsampledwherefilter), which does the same downsampling, but with a more simple query.

### Downsampler

Sampling time-series data on some fixed-time interval is a common method used to reduce the required storage space or for increasing processing speed. While it is possible to perform this operation using a combination of more basic Deephaven operations, there is an existing utility that covers many common applications.

The Downsampler takes a time-series table as input and produces a downsampled version. Other than the timestamp and key columns, each output column is produced by an aggregate: typically either `last`, `min`, `max`, `sum`, `first`, `last`, `std`, `var`, `avg`, or `array`. All aggregates will take match pairs to support renaming the source column (i.e. `"Volume=LastSize"` when a sum aggregate is applied).

Timestamps from a specified column are grouped into "bins" using a method determined by the `timeBinMode` (defaults to `UPPER`, which would put all times between, for example, 3:20 and 3:25 into the 3:25 time bin if 5 minute intervals are used). For some applications, it is useful to have every "time bin" represented, even if there is no data. In this case, one can set the `allBins` option. Sometimes, as in stock quote data, if there is no new data, it is desirable that the value of the prior bin should be used instead of `null`. To produce this behavior, specify the relevant column(s) in the `maintainState` option.

The Downsampler can operate in online mode (default is on), in which case the downsampled table is updated whenever the source changes. If this behavior isn't needed, it is possible to turn off online mode, and run large downsampling jobs using multiple threads in parallel. Online mode supports only single-threaded operation.

Under normal conditions, every time a new row is added to the source table, the downsampled table will "tick". This is because the last time bin is always "active" (i.e., a row at 3:23 will affect the 3:25 bin). This causes a large volume of table updates in the downsampled table (at least one for each new row in the source). Using the `excludeLastBin` option, the last time bin can be excluded from the output until it can no longer change (assuming timestamps increase monotonically), one can eliminate this behavior. This is useful when logging the downsampled output to another table.

**Example 1**

Downsample quotes, while filling in "empty" time bins, and maintaining state.

```python
import jpy

quoteTable = db.t("LearnDeephaven", "StockQuotes")

# import com.illumon.iris.downsampling.Downsampler

Downsampler = jpy.get_type("com.illumon.iris.downsampling.Downsampler")

downsampledQuotes = (
    Downsampler.downsample(db, quoteTable, "Timestamp", "00:05:00", "Sym")
    .allBins(True)
    .last("Bid", "BidSize", "Ask", "AskSize")
    .maintainState("Bid", "BidSize", "Ask", "AskSize")
    .execute()
)
```

```groovy
quoteTable = db.t("LearnDeephaven" , "StockQuotes")

import com.illumon.iris.downsampling.Downsampler

downsampledQuotes = Downsampler.downsample(db, quoteTable, "Timestamp", "00:05:00", "Sym")
   .allBins(true)
   .last("Bid","BidSize","Ask","AskSize")
   .maintainState("Bid","BidSize","Ask","AskSize")
   .execute()
```

**Example 2**

Downsample trades, calculating Volume, High and Low per time bin:

```python
import jpy

tradeTable = db.t("LearnDeephaven", "StockTrades")

from deephaven.ImportTools.DownsampleImport import builder

Downsampler = jpy.get_type("com.illumon.iris.downsampling.Downsampler")
downsampledTrades = (
    Downsampler.downsample(db, tradeTable, "Timestamp", "00:05:00", "Sym")
    .last("Last", "Size")
    .sum("Volume=Size")
    .min("Low=Last")
    .max("High=Last")
    .execute()
)
```

```groovy
tradeTable = db.t("LearnDeephaven", "StockTrades")

import com.illumon.iris.downsampling.Downsampler

downsampledTrades = Downsampler.downsample(db, tradeTable, "Timestamp", "00:05:00", "Sym")
   .last("Last","Size")
   .sum("Volume=Size")
   .min("Low=Last")
   .max("High=Last")
   .execute()
```

> [!NOTE]
> See:
> PyDocs: [deephaven.ImportTool.DownsampleImport](https://docs.deephaven.io/pydocs/code/deephaven.ImportTools.DownsampleImport.html)
>
> JavaDocs: [Downsampler](https://docs.deephaven.io/javadoc/20240517/com/illumon/iris/downsampling/Downsampler.html)

| Setter Method          | Type                                  | Req? | Default               | Description                                                                                                                                           |
| ---------------------- | ------------------------------------- | ---- | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- |
| `timeBinMode`          | String `Downsampler.TimeBinMode`      | No   | `UPPER`               | How to assign source data to time bins (`UPPER`/`LOWER`).                                                                                             |
| `allBins`              | boolean                               | No   | `true`                | Whether to generate all time bins in the source data range, even if no data matches the bin.                                                          |
| `maintainStateColumns` | String                                | No   | N/A                   | Columns for which to maintain state from one time bin to the next if no new data is present for that bin (only applicable if `setAllBins` is `true`). |
| `byStrategy`           | String                                | No   | DEFAULT               | Strategy to use when generating aggregates.                                                                                                           |
| `aJStrategy`           | String                                | No   | DEFAULT               | Strategy to use when using as-of-joins (when `allBins=true`, for maintain-state columns).                                                             |
| `naturalJoinStrategy`  | String                                | No   | DEFAULT               | Strategy to use when joining data to time bins (when `allBins=true`).                                                                                 |
| `setCalendar`          | String                                | No   | N/A                   | Calendar to filter time bins                                                                                                                          |
| `online`               | boolean                               | No   | `true`                | Whether the output table should update when the source table ticks.                                                                                   |
| `numThreads`           | int                                   | No   | 1                     | Number of threads to use for parallel downsampling. Using a larger number of threads may improve performance for large source tables.                 |
| `excludeLastBin`       | boolean                               | No   | `false`               | Whether to exclude the last/active time bin from the output.                                                                                          |
| `logger`               | Logger                                | No   | Default system logger | Override the logger used for logging progress as the downsampler proceeds.                                                                            |
| `aggregates`           | `List<ComboAggregateFactory.ComboBy>` | No   | N/A                   | Add a set of aggregate columns to produce in the output.                                                                                              |
| `aggregate`            | AggType, column                       | No   | N/A                   | Add an aggregate column to produce in the output table.                                                                                               |
| `last`                 | String                                | No   | N/A                   | Add a set of columns to produce with the `lastBy` operator.                                                                                           |
| `first`                | String                                | No   | N/A                   | Add a set of columns to produce with the `firstBy` operator.                                                                                          |
| `min`                  | String                                | No   | N/A                   | Add a set of columns to produce with the `minBy` operator.                                                                                            |
| `max`                  | String                                | No   | N/A                   | Add a set of columns to produce with the `maxBy` operator.                                                                                            |
| `sum`                  | String                                | No   | N/A                   | Add a set of columns to produce with the `sumBy` operator.                                                                                            |
| `std`                  | String                                | No   | N/A                   | Add a set of columns to produce with the `stdBy` operator.                                                                                            |
| `var`                  | String                                | No   | N/A                   | Add a set of columns to produce with the `varBy` operator.                                                                                            |
| `array`                | String                                | No   | N/A                   | Add a set of columns to produce with the `arrayBy` operator.                                                                                          |

## Calendar Methods

### Calendars Class

There are several calendar methods to use in Deephaven. If you want to use the default calendar in your system, calendar methods can be used statically within query strings. For example:

`t2 = t.where("Date = currentDay()")`

However, to use a different calendar other than the default, you must first import the `Calendars` class, as shown below.

> [!IMPORTANT]
> Only the USNYSE calendar is updated by Deephaven. Users will need to maintain any other calendars they wish to use.

```python skip-test
from deephaven import *
jpose = cals.calendar("JPOSE")
t = t.update(“Date = jpose.currentDay()”)
```

```groovy skip-test
import com.illumon.util.calendar.Calendars
jpose = Calendars.calendar("JPOSE")
t = t.where(“Date = jpose.currentDay()”)
```

There are three primary methods to access these calendars:

1. You can specify the calendar by name. As demonstrated above, `Calendars.calendar(“name”)`. Note: Use the `calendarNames()` method to get a list of available calendar names: `print cals.calendarNames()` or `println Calendars.calendarNames()`.
2. You can get the default calendar as specified in your configurations, or you can set the default calendar with the `calendar()` method; e.g., `USNYSE = Calendars.calendar()` or `USNYSE = cals.calendar()`, or you can set the default calendar under **Settings**.
3. You can use the provided calendars as variables (only in Groovy).

For example, if you wanted to use the provided USNYSE calendar in your query, you could use the following:

`curDayNY = CALENDAR_USNYSE.currentDay()`

Or equivalently:

```python
from deephaven import *  # Calendars module imported as cals

curDayNY = cals.calendar("USNYSE").currentDay()
```

```groovy
import com.illumon.util.calendar.Calendars
curDayNY = Calendars.calendar("USNYSE").currentDay()
```

| Method                        | Description                                   |
| ----------------------------- | --------------------------------------------- |
| `calendar(final String name)` | Returns a specified business calendar.        |
| `calendar()`                  | Returns the default business calendar.        |
| `calendarNames()`             | Returns the names of all available calendars. |

### Calendar Methods

Once the class is imported, you can employ any of the calendar methods that follow to obtain time and date related information via queries.

> [!NOTE]
> The methods that take no date parameter act on the current date. For example `nextDay(5)` is equivalent to `nextDay(currentDay(), 5)`.

```python
from deephaven.Calendars import calendarNames, calendar

print(calendarNames())

c = calendar("USNYSE")
print(c)
print(c.dayOfWeek())
```

```groovy skip-test
println com.illumon.util.calendar.Calendars.calendar(“USNYSE”).dayOfWeek(“2020-12-07")

println com.illumon.util.calendar.Calendars.calendar(“USNYSE”).dayOfWeek(DBTimeUtils.convertDateTime(“2020-12-07T00:00:00 LON”))

println com.illumon.util.calendar.Calendars.calendar(“USNYSE”).dayOfWeek(DBTimeUtils.convertDateTime(“2020-12-07T00:00:00 NY”))
```

In addition to the general Calendar methods below, there are also separate methods for [business-specific calendars](#business-calendars-schedules-and-periods).

| Method                                                                                                                                                                              | Description                                                                                            |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------ |
| <ul><li>`dayOfWeek()`</li> <li>`dayOfWeek(DBDateTime time)`</li><li>`dayOfWeek(java.lang.String date)`</li></ul>                                                                    | Gets the day of the week for a time.                                                                   |
| <ul><li>`daysInRange(DBDateTime start, DBDateTime end)`</li><li>`daysInRange(java.lang.String start, java.lang.String end)`</li></ul>                                               | Gets the days in a given range.                                                                        |
| `diffDay(DBDateTime start, DBDateTime end)`                                                                                                                                         | Returns the amount of time in days between start and end.                                              |
| `diffNanos(DBDateTime start, DBDateTime end)`                                                                                                                                       | Returns the amount of time in nanoseconds between start and end.                                       |
| `diffYear(DBDateTime start, DBDateTime end)`                                                                                                                                        | Returns the number of years between start and end.                                                     |
| `name()`                                                                                                                                                                            | Gets the name of the calendar.                                                                         |
| <ul><li>`nextDay()`</li><li>`nextDay(DBDateTime time)`</li><li>`nextDay(java.lang.String date)`</li></ul>                                                                           | Gets the next date.                                                                                    |
| <ul><li>`nextDay(int days)`</li><li>`nextDay(DBDateTime time, int days)`</li><li>`nextDay(java.lang.String date, int days)`</li></ul>                                               | Gets the date specified by the number of days after the input date.                                    |
| <ul><li>`numberOfDays(DBDateTime start, DBDateTime end)`</li><li>`numberOfDays(java.lang.String start, java.lang.String end)`</li></ul>                                             | Gets the number of days in a given range, end date exclusive.                                          |
| <ul><li>`numberOfDays(DBDateTime start, DBDateTime end, boolean endInclusive)`</li><li>`numberOfDays(java.lang.String start, java.lang.String end, boolean endInclusive)`</li></ul> | Gets the number of days in a given range, with the option to make the end date inclusive or exclusive. |
| <ul><li>`previousDay()`</li><li>`previousDay(DBDateTime time)`</li><li>`previousDay(java.lang.String date)`</li></ul>                                                               | Gets the previous date.                                                                                |
| <ul><li>`previousDay(int days)`</li><li>`previousDay(DBDateTime time, int days)`</li><li>`previousDay(java.lang.String date, int days)`</li></ul>                                   | Gets the date specified by the number of days before the input date.                                   |
| `timeZone()`                                                                                                                                                                        | Gets the timezone of the calendar.                                                                     |

### Available Timezones

`DBDateTime` strings use the following codes. For example, the following query uses the time zone for London:

```groovy skip-test
t2=db.t("LSE","LSELondon").where("Date=`2020-04-01`").where("TIMESTAMP > '2020-04-01T08:00:00 LON'")
```

| String | DateTimeZone ID                                   | Timezone                   |
| ------ | ------------------------------------------------- | -------------------------- |
| `NY`   | `TZ_NY(DateTimeZone.forID("America/New_York")`    | America/New York           |
| `ET`   | `TZ_ET(DateTimeZone.forID("America/New_York")`    | America/New York           |
| `MTL`  | `TZ_MTL("America/Montreal")`                      | America/Montreal           |
| `MEX`  | `TZ_MEX("America/Mexico_City")`                   | America/Mexico City        |
| `MN`   | `TZ_MN(DateTimeZone.forID("America/Chicago")`     | America/Chicago            |
| `CT`   | `TZ_CT(DateTimeZone.forID("America/Chicago")`     | America/Chicago            |
| `MT`   | `TZ_MT(DateTimeZone.forID("America/Denver")`      | America/Denver             |
| `PT`   | `TZ_PT(DateTimeZone.forID("America/Los_Angeles")` | America/Los Angeles        |
| `HI`   | `TZ_HI(DateTimeZone.forID("Pacific/Honolulu")`    | Pacific/Honolulu           |
| `BT`   | `TZ_BT(DateTimeZone.forID("America/Sao_Paulo")`   | America/Sao Paulo          |
| `KR`   | `TZ_KR(DateTimeZone.forID("Asia/Seoul")`          | Asia/Seoul                 |
| `HK`   | `TZ_HK(DateTimeZone.forID("Asia/Hong_Kong")`      | Asia/Hong Kong             |
| `JP`   | `TZ_JP(DateTimeZone.forID("Asia/Tokyo")`          | Asia/Tokyo                 |
| `IN`   | `TZ_IN(DateTimeZone.forID("Asia/Kolkata")`        | Asia/Kolkata               |
| `SG`   | `TZ_SG(DateTimeZone.forID("Asia/Singapore")`      | Asia/Singapore             |
| `SHG`  | `TZ_SHG(DateTimeZone.forID("Asia/Shanghai")`      | Asia/Shanghai              |
| `TW`   | `TZ_TW(DateTimeZone.forID("Asia/Taipei")`         | Asia/Tapei                 |
| `KL`   | `TZ_KL("Asia/Kuala_Lumpur"`                       | Asia/Kuala Lumpur          |
| `AT`   | `TZ_AT(DateTimeZone.forID("Canada/Atlantic")`     | Canada/Atlantic            |
| `NF`   | `TZ_NF(DateTimeZone.forID("Canada/Newfoundland")` | Canada/Newfoundland        |
| `AL`   | `TZ_AL(DateTimeZone.forID("America/Anchorage")`   | America/Anchorage          |
| `CE`   | `TZ_CE(DateTimeZone.forID("Europe/Berlin")`       | Europe/Berlin              |
| `SKM`  | `TZ_SKM("Europe/Stockholm")`                      | Europe/Stockholm           |
| `OSL`  | `TZ_OSL("Europe/Oslo")`                           | Europe/Oslo                |
| `MAD`  | `TZ_MAD("Europe/Madrid")`                         | Europe/Madrid              |
| `LON`  | `TZ_LON(DateTimeZone.forID("Europe/London")`      | Europe/London              |
| `MOS`  | `TZ_MOS(DateTimeZone.forID("Europe/Moscow")`      | Europe/Moscow              |
| `CH`   | `TZ_CH(DateTimeZone.forID("Europe/Zurich")`       | Europe/Zurich              |
| `NL`   | `TZ_NL(DateTimeZone.forID("Europe/Amsterdam")`    | Europe/Amsterdam           |
| `JNB`  | `TZ_JNB("Africa/Johannesburg")`                   | Africa/Johannesburg        |
| `SYD`  | `TZ_SYD(DateTimeZone.forID("Australia/Sydney")`   | Australia/Sydney           |
| `UTC`  | `TZ_UTC(DateTimeZone.UTC)`                        | Coordinated Universal Time |

## Business Calendars, Schedules, and Periods

Business calendars are used in Deephaven to state when a business entity is operational (or not), including the hours, days and years of regular operations, as well as holidays.

For example, one of the business calendars installed with Deephaven presents temporal information pertaining to the New York Stock Exchange (NYSE), which is open Monday through Friday, from 9:30 a.m. to 4 p.m. Eastern time. The calendar also includes information about the exchange's nine full-day holidays and its partial holidays, which vary from year to year.

A collection of 12 business calendars are included when Deephaven is installed. However, you can also create and install your own custom Business Calendar.

### Creating Custom Business Calendars

Business calendars are stored in files that use XML formatting to specify the aspects of the calendar, including the timezone, hours and days of the week the business is operational, as well as holidays.

The root element of the XML file is `<calendar>`, and there are four children elements:

- `<name>` is the name of the business calendar (required).
- `<timeZone>` provides the time zone used for the calendar (required).
- `<default>` provides information about regular hours and days the business is and is not operational.
- `<holiday>` provides information about individual holidays (special days/times for which the business is non-operational).

#### `name`

In the following example, "Colorado" is the name of the calendar:

`<name>Colorado</name>`

#### `timeZone`

The `timeZone` element provides the timezone used for the calendar in Deephaven. For our example calendar, `TZ_MT` is the timezone used (U.S. Mountain Time), as shown below.

`<timeZone>TZ_MT</timeZone>`

#### `default`

There are two components included in the default element:

- `businessPeriod` - provides the hours of the day that business operations are conducted. The values listed are the opening time and closing time separated by a comma, using a 24-hour clock in the timezone noted in the `timeZone` element. Note: More than one `businessPeriod` can be included in the default element. For example, if a business closes regularly for lunch, there could be two `businessPeriod` components in the default element - one for the period before lunch and one for the period after lunch.
- `weekend` - provides the day of the week that business operations are not operational. Each weekend day is presented individually. This is an optional component. If there are no weekends included in the default element, Deephaven will assume the business is operational seven days per week. For our example calendar, we will assume that business in Colorado is operational from 9 a.m. to 6 p.m., Monday through Friday, as shown below:

```xml
<default>
    <businessPeriod>09:00,18:00</businessPeriod>
    <weekend>Saturday</weekend>
    <weekend>Sunday</weekend>
</default>
```

#### `holiday`

There are two components included in the holiday element:

- `date` - provides the year, month and date of the holiday in the format `YYYYMMDD`. Each individual holiday must be presented by itself, including a separate record for each year in which it is celebrated. If date is not included in the holiday element, Deephaven will assume there are no holidays.
- `businessPeriod` - provides the hours of the day that business operations are conducted. The values listed are the starting time and ending time separated by a comma, using a 24-hour clock in the timezone noted above. If business operations are closed for the entire day, this element is not required.

For our example calendar, we will assume Colorado has full-day holidays for Zebulon Pike's birthday on January 5, Colorado Gold Rush Day on July 11, the anniversary of Colorado statehood on August 1, and Stephen Long's birthday on December 30. We will also specify a partial holiday for Dr. Edwin James's birthday on August 27. These holidays (as celebrated in 2017) are shown below:

```xml
<holiday>
    <date>20170105</date>
</holiday>
<holiday>
    <date>20170711</date>
</holiday>
<holiday>
    <date>20171230</date>
</holiday>
<holiday>
    <date>20170801</date>
</holiday>
<holiday>
    <date>20170827</date>
    <businessPeriod>09:00,12:00</businessPeriod>
</holiday>
```

The entire XML file for a Business Calendar named "Colorado" follows:

<details>
<summary>colorado.calendar</summary>

```xml
<calendar>
    <name>Colorado</name>
    <timeZone>TZ_MT</timeZone>
    <default>
       <businessPeriod>09:00,18:00</businessPeriod>
       <weekend>Saturday</weekend>
       <weekend>Sunday</weekend>
    </default>
    <holiday>
       <date>20170105</date>
    </holiday>
    <holiday>
        <date>20170711</date>
    </holiday>
    <holiday>
        <date>20171230</date>
    </holiday>
    <holiday>
        <date>20170801</date>
    </holiday>
    <holiday>
        <date>20170827</date>
        <businessPeriod>09:00,12:00</businessPeriod>
    </holiday>
</calendar>
```

</details>

### Installing Custom Business Calendars

Once a new custom Business Calendar is saved with the .calendar suffix, your system administrator will need to install the file(s) to the Deephaven server(s). Instructions for the system administrator are included in [Installing Custom Calendars and Custom Plotting Themes](../../sys-admin/optional-settings/custom-calendars.md).

### Business Calendar Methods

As shown below, there are many methods specific to Business Calendars that are available to help users obtain information about the times and dates in which a business operates.

> [!NOTE]
> Just like [Calendar methods](#calendar-methods), the Business Calendar methods that take no date parameter act on the current date, e.g., `nextBusinessDay(5)` is equivalent to `nextBusinessDay(currentDay(), 5)`.

| Method                                                                                                                                                                                                                                                                                                                                                        | Description                                                                                                            |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- |
| <ul><li>`businessDaysInRange(DBDateTime start, DBDateTime end)`</li><li>`businessDaysInRange(java.lang.String start, java.lang.String end)`</li></ul>                                                                                                                                                                                                         | Returns the business days between start and end, inclusive.                                                            |
| `diffBusinessDay(DBDateTime start, DBDateTime end)`                                                                                                                                                                                                                                                                                                           | Returns the amount of business time in standard business days between start and end.                                   |
| `diffBusinessNanos(DBDateTime start, DBDateTime end)`                                                                                                                                                                                                                                                                                                         | Returns the amount of business time in nanoseconds between start and end.                                              |
| `diffBusinessYear(DBDateTime start, DBDateTime end)`                                                                                                                                                                                                                                                                                                          | Returns the number of business years between start and end.                                                            |
| `diffNonBusinessDay(DBDateTime start, DBDateTime end)`                                                                                                                                                                                                                                                                                                        | Returns the amount of non-business time in standard business days between start and end.                               |
| `diffNonBusinessNanos(DBDateTime start, DBDateTime end)`                                                                                                                                                                                                                                                                                                      | Returns the amount of non-business time in nanoseconds between start and end.                                          |
| `fractionOfBusinessDayComplete(DBDateTime time)`                                                                                                                                                                                                                                                                                                              | Returns the fraction of the business day complete by the given time.                                                   |
| `fractionOfBusinessDayRemaining(DBDateTime time)`                                                                                                                                                                                                                                                                                                             | Returns the fraction of the business day remaining after the given time.                                               |
| <ul><li>`fractionOfStandardBusinessDay(DBDateTime time)`</li><li>`fractionOfStandardBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                                             | For the given date, returns the ratio of the business day length and the standard business day length.                 |
| <ul><li>`getBusinessSchedule(DBDateTime time)`</li><li>`getBusinessSchedule(String date)`</li><li>`getBusinessSchedule(LocalDate date)`</li></ul>                                                                                                                                                                                                             | Gets the indicated business schedule.                                                                                  |
| `isBusinessDay(DBDateTime time)`                                                                                                                                                                                                                                                                                                                              | Does time occur on a business day?                                                                                     |
| <ul><li>`isBusinessDay()`</li><li>`isBusinessDay(java.time.LocalDate date)`</li><li>`isBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                                          | Is the date a business day?                                                                                            |
| `isBusinessTime(DBDateTime time)`                                                                                                                                                                                                                                                                                                                             | Determines if the specified time is a business time.                                                                   |
| <ul><li>`isLastBusinessDayOfMonth()`</li><li>`isLastBusinessDayOfMonth(DBDateTime time)`</li><li>`isLastBusinessDayOfMonth(java.lang.String date)`</li></ul>                                                                                                                                                                                                  | Is the time listed the last day of the month, and is there time left in the business day?                              |
| <ul><li>`isLastBusinessDayOfWeek()`</li><li>`isLastBusinessDayOfWeek(DBDateTime time)`</li><li>`isLastBusinessDayOfWeek(java.lang.String date)`</li></ul>                                                                                                                                                                                                     | Is the time listed the last day of the week, and is there time left in the business day?                               |
| <ul><li>`nextBusinessDay()`</li><li>`nextBusinessDay(DBDateTime time)`</li><li>`nextBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                                             | Gets the next business day.                                                                                            |
| <ul><li>`nextBusinessDay(int n)`</li><li>`nextBusinessDay(DBDateTime time, int n)`</li><li>`nextBusinessDay(java.lang.String date, int n)`</li></ul>                                                                                                                                                                                                          | Gets the next business date that is `n` business days after input time.                                                |
| <ul><li>`nextBusinessSchedule()`</li><li>`nextBusinessSchedule(DBDateTime time)`</li><li>`nextBusinessSchedule(java.lang.String date)`</li></ul>                                                                                                                                                                                                              | Gets the next business schedule. A business schedule is both the date and the hours the business is open on that date. |
| <ul><li>`nextBusinessSchedule(int n)`</li><li>`nextBusinessSchedule(DBDateTime time, int n)`</li><li>`nextBusinessSchedule(java.lang.String date, int n)`</li></ul>                                                                                                                                                                                           | Gets the next business schedule that is `n` business days before input time.                                           |
| <ul><li>`nextNonBusinessDay()`</li><li>`nextNonBusinessDay(DBDateTime time)`</li><li>`nextNonBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                                    | Gets the next non-business day.                                                                                        |
| <ul><li>`nextNonBusinessDay(int n)`</li><li>`nextNonBusinessDay(DBDateTime time, int n)`</li><li>`nextNonBusinessDay(java.lang.String date, int n)`</li></ul>                                                                                                                                                                                                 | Gets the next non-business date that is `n` non-business days after input time.                                        |
| <ul><li>`nonBusinessDaysInRange(DBDateTime start, DBDateTime end)`</li><li>`nonBusinessDaysInRange(java.lang.String start, java.lang.String end)`</li></ul>                                                                                                                                                                                                   | Returns the non-business days between start and end, inclusive.                                                        |
| <ul><li>`numberOfBusinessDays(DBDateTime start, DBDateTime end)`</li><li>`numberOfBusinessDays(java.lang.String start, java.lang.String end)`</li><li>`numberOfBusinessDays(DBDateTime start, DBDateTime end, boolean endInclusive)`</li><li>`numberOfBusinessDays(java.lang.String start, java.lang.String end, boolean endInclusive)`</li> </ul>            | Returns the number of business days between start and end.                                                             |
| <ul><li>`numberOfNonBusinessDays(DBDateTime start, DBDateTime end)`</li><li>`numberOfNonBusinessDays(DBDateTime start, DBDateTime end, boolean endInclusive)`</li><li>`numberOfNonBusinessDays(java.lang.String start, java.lang.String end)`</li><li>`numberOfNonBusinessDays(java.lang.String start, java.lang.String end, boolean endInclusive)`</li></ul> | Returns the number of non-business days between start and end.                                                         |
| <ul><li>`previousBusinessDay()`</li><li>`previousBusinessDay(DBDateTime time)`</li><li>`previousBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                                 | Gets the previous business day.                                                                                        |
| <ul><li>`previousBusinessDay(int n)`</li><li>`previousBusinessDay(DBDateTime time, int n)`</li><li>`previousBusinessDay(java.lang.String date, int n)`</li></ul>                                                                                                                                                                                              | Gets the next business date that is `n` business days before input time.                                               |
| <ul><li>`previousBusinessSchedule()`</li><li>`previousBusinessSchedule(DBDateTime time)`</li><li>`previousBusinessSchedule(java.lang.String date)`</li></ul>                                                                                                                                                                                                  | Gets the previous business schedule.                                                                                   |
| <ul><li>`previousBusinessSchedule(int n)`</li><li>`previousBusinessSchedule(DBDateTime time, int n)`</li><li>`previousBusinessSchedule(java.lang.String date, int n)`</li></ul>                                                                                                                                                                               | Gets the next business schedule that is `n` business days before input time.                                           |
| <ul><li>`previousNonBusinessDay()`</li><li>`previousNonBusinessDay(DBDateTime time)`</li><li>`previousNonBusinessDay(java.lang.String date)`</li></ul>                                                                                                                                                                                                        | Gets the previous non-business day.                                                                                    |
| <ul><li>`previousNonBusinessDay(int n)`</li><li>`previousNonBusinessDay(DBDateTime time, int n)`</li><li>`previousNonBusinessDay(java.lang.String date, int n)`</li></ul>                                                                                                                                                                                     | Gets the next non-business date that is `n` non-business days before input time.                                       |
| `standardBusinessDayLengthNanos()`                                                                                                                                                                                                                                                                                                                            | Returns the length of a standard business day in nanoseconds.                                                          |

### Business Schedule Methods

A Business Schedule is the collection of periods within a 24-hour day when a business is open. Business Schedules may have multiple periods. If a business is open continuously from 8 a.m. to 9 p.m., it would be open for one period. However, if the business is closed daily for lunch, it would have two periods.

For example, on August 21, 2017, using the USNYSE calendar, the `BusinessSchedule` would look like this:

`2017-08-21 9:30 NY to 2017-08-21 16:00 NY`

| Method                                                           | Description                                                                                                                                |
| ---------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `getBusinessPeriods()`                                           | Gets the business periods for the day.                                                                                                     |
| <ul><li>`getSOBD()`</li><li>`getStartOfBusinessDay()`</li></ul>  | Gets the starting time of the business day.                                                                                                |
| <ul><li>`getEOBD()`</li><li>`getEndOfBusinessDay()`</li></ul>    | Gets the end time of the business day.                                                                                                     |
| <ul><li>`getLOBD()`</li><li>`getLengthOfBusinessDay()`</li></ul> | Gets the length of the business day in nanoseconds. If the business day has multiple periods, only the time during the periods is counted. |
| <ul><li>`isBusinessDay()`</li></ul>                              | Is this day a business day? Returns `true` if yes; `false` otherwise.                                                                      |
| `isBusinessTime(final DBDateTime time)`                          | Determines if the specified time is a business time for the day. Returns `true` if yes; `false` otherwise.                                 |
| `businessTimeElapsed(final DBDateTime time)`                     | Returns the amount of business time in nanoseconds that has elapsed on the given day by the specified time.                                |

### Business Period Methods

A Business Period is a continuous block of time in which the business is open. Available methods give the start, end, and length of a Business Period, as well as whether a time occurs during the period.

| Method                            | Description                                                     |
| --------------------------------- | --------------------------------------------------------------- |
| `getStartTime()`                  | Returns the start of the period.                                |
| `getEndTime()`                    | Returns the end of the period.                                  |
| `getLength()`                     | Returns the length of the period in nanoseconds.                |
| `contains(final DBDateTime time)` | Determines if the specified time is within the business period. |

## Temporal Examples

```python skip-test
# Filter the table to return trade data for only one specific date inside of the query language

trades0825 = db.t("LearnDeephaven", "StockTrades").where("Date=`2017-08-25`")

# Filter the table to show only the current date's trades. In this example, the current date is stored as a user-designated variable in the first line (e.g., 2017-08-25).

CurrentDate = "2017-08-25"
tradesCurrentDate = db.t("LearnDeephaven", "StockTrades").where("Date=CurrentDate")

# Using java.lang.String methods on the Date (Date is a java String object) to show trades that (a) are not null and (b) occurred in the month of August 2017.

trades08 = db.t("LearnDeephaven", "StockTrades").where(
    "Date != null && Date.startsWith(`2017-08`)"
)

# Filters using comparators to dates in August 2017.
trades08Comparator = db.t("LearnDeephaven", "StockTrades").where(
    "Date > `2017-08` && Date < `2017-09`"
)

# Equivalent to above except the ranges are inclusive.
trades08InRange = db.t("LearnDeephaven", "StockTrades").where(
    "inRange(Date, `2017-08-01`, `2017-09-01`)"
)

# To construct DBDateTimes in the query language, use the single quote.

# All dates after 8AM 2017-08-21. Comparison operators on DBDateTimes are supported in the query language strings.

trades08After8 = trades08.where("Timestamp > '2017-08-21T08:00 NY'")

# 8AM - 5PM 2017-08-21
trades08BusinessHours1 = trades08.where(
    "Timestamp >= '2017-08-21T08:00 NY' && Timestamp <= '2017-08-21T17:00 NY'"
)

# Equivalent to above.
trades08BusinessHours2 = trades08.where(
    "inRange(Timestamp, '2017-08-21T08:00 NY', '2017-08-21T17:00 NY')"
)

# DBDateTimes supports simple operands as well.

# Predefined DBDateTime variables are SECOND, MINUTE, HOUR, DAY, WEEK, and YEAR. These are included statically in the query language strings.

# This query filters the table to [baseTime, baseTime + one hour].
table = table.where("Timestamp > baseTime && Timestamp < (baseTime + HOUR)")
from deephaven import *

date = "2019-10-18"
startOfDay = cals.calendar("USNYSE").getBusinessSchedule(date).getStartOfBusinessDay()
t = db.t("MarketUs", "TradeNbboStock").where(
    "Date = date", "Timestamp > startOfDay && Timestamp < (startOfDay + HOUR)"
)

# Transforming DBDateTimes to equivalent nanos from Epoch.

trades08BusinessHours3 = trades08.updateView("TimestampNanos = nanos(Timestamp)")

# Uses the default calendar to determine business hours, e.g., USNYSE calendar 930AM - 4PM 2017-08-21.
# The default calendar is statically imported for use in query language strings.
trades08BusinessHours4 = trades08.where("isBusinessTime(Timestamp)")

# Calendar for the next three days.
# Note that defining the nextDay(3) variable will make this significantly faster than using nextDay(3) in the where().
# Import the java StaticCalendarMethods class into the binding.
importjava("com.illumon.util.calendar.StaticCalendarMethods")
current_day = StaticCalendarMethods.currentDay()
current_day_plus3 = StaticCalendarMethods.nextDay(3)

# Date will be >= currentDay and <= currentDay + 3
trades08BusinessHours5 = trades08.where("inRange(Date, current_day, current_day_plus3)")

# All Mondays of August 2017.
trades08Mondays1 = trades08.where("dayOfWeek(Date).getValue() = 1")

# Equivalent to above.
trades08Mondays2 = trades08.where("dayOfWeek(Date) = java.time.DayOfWeek.MONDAY")

# timeTable is useful for table snapshots.
# Creates a ticking table which gets a new row every second.
tt = db.timeTable("00:00:01")
```

```groovy
// Filter the table to return trade data for only one specific date inside of the query language
trades0825 = db.t("LearnDeephaven","StockTrades").where("Date=`2017-08-25`")

// Filter the table to show only the current date's trades. In this example, the current date is stored as a user-designated variable in the first line (e.g., 2017-08-25).
CurrentDate = "2017-08-25"
tradesCurrentDate = db.t("LearnDeephaven","StockTrades").where("Date=CurrentDate")

// Using java.lang.String methods on the Date (Date is a java String object) to show trades that (a) are not null and (b) occurred in the month of August 2017.
trades08 = db.t("LearnDeephaven","StockTrades").where("Date != null && Date.startsWith(`2017-08`)")

// Filters to dates in August 2017.
trades08Comparator = db.t("LearnDeephaven","StockTrades").where("Date > `2017-08` && Date < `2017-09`")

// Equivalent to above except the ranges are inclusive.
trades08InRange = db.t("LearnDeephaven","StockTrades").where("inRange(Date, `2017-08-01`, `2017-09-01`)")

// To construct DBDateTimes in the query language, use the single quote.

// All dates after 8AM 2017-08-21. Comparison operators on DBDateTimes are supported in the query language strings.

trades08After8 = trades08.where("Timestamp > '2017-08-21T08:00 NY'")

// 8AM - 5PM 2017-08-21
trades08BusinessHours1 = trades08.where("Timestamp >= '2017-08-21T08:00 NY' && Timestamp <= '2017-08-21T17:00 NY'")

// Equivalent to above.
trades08BusinessHours2 = trades08.where("inRange(Timestamp, '2017-08-21T08:00 NY', '2017-08-21T17:00 NY')")

// DBDateTimes supports simple operands as well.

// Predefined DBDateTime variables are SECOND, MINUTE, HOUR, DAY, WEEK, and YEAR. These are included statically in the query language strings.

// This query filters the table to [baseTime, baseTime + one hour].
import com.illumon.util.calendar.Calendars;
date = "2019-10-18"
startOfDay = Calendars.calendar("USNYSE").getBusinessSchedule(date).getStartOfBusinessDay()
t = db.t("MarketUs", "TradeNbboStock").where("Date = date", "Timestamp > startOfDay && Timestamp < (startOfDay + HOUR)")

// Transforming DBDateTimes to equivalent nanos from Epoch.

trades08BusinessHours3 = trades08.updateView("TimestampNanos = nanos(Timestamp)")

// Uses the default calendar to determine business hours, e.g., USNYSE calendar 930AM - 4PM 2017-08-21.
// The default calendar is statically imported for use in query language strings.
trades08BusinessHours4 = trades08.where("isBusinessTime(Timestamp)")

// Calendar for the next three days.
// Note that defining the nextDay(3) variable will make this significantly faster than using nextDay(3) in the where().
// Import the java StaticCalendarMethods class into the binding.
import com.illumon.util.calendar.StaticCalendarMethods
currentDay = StaticCalendarMethods.currentDay()
currentDayPlus3 = StaticCalendarMethods.nextDay(3)

// Date will be >= currentDay and <= currentDay + 3.
trades08BusinessHours5 = trades08.where("inRange(Date, currentDay, currentDayPlus3)")

// All Mondays of August 2017.
trades08Mondays1 = trades08.where("dayOfWeek(Date).getValue() = 1")

// Equivalent to above.
trades08Mondays2 = trades08.where("dayOfWeek(Date) = java.time.DayOfWeek.MONDAY")

// timeTable is useful for table snapshots.
// Creates a ticking table which gets a new row every second.
tt = db.timeTable('00:00:01')
```