Working with time

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:

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, 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.

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()

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()

Example 2

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

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()

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()

Note

See: PyDocs: deephaven.ImportTool.DownsampleImport

JavaDocs: Downsampler

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.

import com.illumon.util.calendar.Calendars
jpose = Calendars.calendar("JPOSE")
t = t.where(“Date = jpose.currentDay()”)

from deephaven import *
jpose = cals.calendar("JPOSE")
t = t.update(“Date = jpose.currentDay()”)

There are three primary methods to access these calendars:

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().
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.
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:

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

from deephaven import *  # Calendars module imported as cals

curDayNY = cals.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).

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”))

from deephaven.Calendars import calendarNames, calendar

print(calendarNames())

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

In addition to the general Calendar methods below, there are also separate methods for business-specific calendars.

Method	Description
`dayOfWeek()` `dayOfWeek(DBDateTime time)` `dayOfWeek(java.lang.String date)`	Gets the day of the week for a time.
`daysInRange(DBDateTime start, DBDateTime end)` `daysInRange(java.lang.String start, java.lang.String end)`	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.
`nextDay()` `nextDay(DBDateTime time)` `nextDay(java.lang.String date)`	Gets the next date.
`nextDay(int days)` `nextDay(DBDateTime time, int days)` `nextDay(java.lang.String date, int days)`	Gets the date specified by the number of days after the input date.
`numberOfDays(DBDateTime start, DBDateTime end)` `numberOfDays(java.lang.String start, java.lang.String end)`	Gets the number of days in a given range, end date exclusive.
`numberOfDays(DBDateTime start, DBDateTime end, boolean endInclusive)` `numberOfDays(java.lang.String start, java.lang.String end, boolean endInclusive)`	Gets the number of days in a given range, with the option to make the end date inclusive or exclusive.
`previousDay()` `previousDay(DBDateTime time)` `previousDay(java.lang.String date)`	Gets the previous date.
`previousDay(int days)` `previousDay(DBDateTime time, int days)` `previousDay(java.lang.String date, int days)`	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:

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:

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

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

colorado.calendar

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

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.

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, 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
`businessDaysInRange(DBDateTime start, DBDateTime end)` `businessDaysInRange(java.lang.String start, java.lang.String end)`	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.
`fractionOfStandardBusinessDay(DBDateTime time)` `fractionOfStandardBusinessDay(java.lang.String date)`	For the given date, returns the ratio of the business day length and the standard business day length.
`getBusinessSchedule(DBDateTime time)` `getBusinessSchedule(String date)` `getBusinessSchedule(LocalDate date)`	Gets the indicated business schedule.
`isBusinessDay(DBDateTime time)`	Does time occur on a business day?
`isBusinessDay()` `isBusinessDay(java.time.LocalDate date)` `isBusinessDay(java.lang.String date)`	Is the date a business day?
`isBusinessTime(DBDateTime time)`	Determines if the specified time is a business time.
`isLastBusinessDayOfMonth()` `isLastBusinessDayOfMonth(DBDateTime time)` `isLastBusinessDayOfMonth(java.lang.String date)`	Is the time listed the last day of the month, and is there time left in the business day?
`isLastBusinessDayOfWeek()` `isLastBusinessDayOfWeek(DBDateTime time)` `isLastBusinessDayOfWeek(java.lang.String date)`	Is the time listed the last day of the week, and is there time left in the business day?
`nextBusinessDay()` `nextBusinessDay(DBDateTime time)` `nextBusinessDay(java.lang.String date)`	Gets the next business day.
`nextBusinessDay(int n)` `nextBusinessDay(DBDateTime time, int n)` `nextBusinessDay(java.lang.String date, int n)`	Gets the next business date that is `n` business days after input time.
`nextBusinessSchedule()` `nextBusinessSchedule(DBDateTime time)` `nextBusinessSchedule(java.lang.String date)`	Gets the next business schedule. A business schedule is both the date and the hours the business is open on that date.
`nextBusinessSchedule(int n)` `nextBusinessSchedule(DBDateTime time, int n)` `nextBusinessSchedule(java.lang.String date, int n)`	Gets the next business schedule that is `n` business days before input time.
`nextNonBusinessDay()` `nextNonBusinessDay(DBDateTime time)` `nextNonBusinessDay(java.lang.String date)`	Gets the next non-business day.
`nextNonBusinessDay(int n)` `nextNonBusinessDay(DBDateTime time, int n)` `nextNonBusinessDay(java.lang.String date, int n)`	Gets the next non-business date that is `n` non-business days after input time.
`nonBusinessDaysInRange(DBDateTime start, DBDateTime end)` `nonBusinessDaysInRange(java.lang.String start, java.lang.String end)`	Returns the non-business days between start and end, inclusive.
`numberOfBusinessDays(DBDateTime start, DBDateTime end)` `numberOfBusinessDays(java.lang.String start, java.lang.String end)` `numberOfBusinessDays(DBDateTime start, DBDateTime end, boolean endInclusive)` `numberOfBusinessDays(java.lang.String start, java.lang.String end, boolean endInclusive)`	Returns the number of business days between start and end.
`numberOfNonBusinessDays(DBDateTime start, DBDateTime end)` `numberOfNonBusinessDays(DBDateTime start, DBDateTime end, boolean endInclusive)` `numberOfNonBusinessDays(java.lang.String start, java.lang.String end)` `numberOfNonBusinessDays(java.lang.String start, java.lang.String end, boolean endInclusive)`	Returns the number of non-business days between start and end.
`previousBusinessDay()` `previousBusinessDay(DBDateTime time)` `previousBusinessDay(java.lang.String date)`	Gets the previous business day.
`previousBusinessDay(int n)` `previousBusinessDay(DBDateTime time, int n)` `previousBusinessDay(java.lang.String date, int n)`	Gets the next business date that is `n` business days before input time.
`previousBusinessSchedule()` `previousBusinessSchedule(DBDateTime time)` `previousBusinessSchedule(java.lang.String date)`	Gets the previous business schedule.
`previousBusinessSchedule(int n)` `previousBusinessSchedule(DBDateTime time, int n)` `previousBusinessSchedule(java.lang.String date, int n)`	Gets the next business schedule that is `n` business days before input time.
`previousNonBusinessDay()` `previousNonBusinessDay(DBDateTime time)` `previousNonBusinessDay(java.lang.String date)`	Gets the previous non-business day.
`previousNonBusinessDay(int n)` `previousNonBusinessDay(DBDateTime time, int n)` `previousNonBusinessDay(java.lang.String date, int n)`	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.
`getSOBD()` `getStartOfBusinessDay()`	Gets the starting time of the business day.
`getEOBD()` `getEndOfBusinessDay()`	Gets the end time of the business day.
`getLOBD()` `getLengthOfBusinessDay()`	Gets the length of the business day in nanoseconds. If the business day has multiple periods, only the time during the periods is counted.
`isBusinessDay()`	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


// 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')


# 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')

Working with time

Temporal Units

DateTime

Period

Durations/Times (part of a DBDateTime)

DateTime Methods

Downsampling Temporal Data

Binning Intervals

Downsampler

Calendar Methods

Calendars Class

Calendar Methods

Available Timezones

Business Calendars, Schedules, and Periods

Creating Custom Business Calendars

name

timeZone

default

holiday

Installing Custom Business Calendars

Business Calendar Methods

Business Schedule Methods

Business Period Methods

Temporal Examples

Durations/Times (part of a `DBDateTime`)

`name`

`timeZone`

`default`

`holiday`