Temporal Data

Temporal data can be used and stored in Deephaven using DateTimes, Periods, and Durations/Times.

DateTime

A DateTime can be represented with the following patterns: yyyy-MM-ddThh:mm:ss.millis/micros/nanos TZ.   This gets converted into a DBDateTime.

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

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

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.

See DownsampledWhereFilter

Downsampling

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

Typical examples:

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

import com.illumon.iris.downsampling.Downsampler
downsampledQuotes = Downsampler.downsample(db, quoteTable, "Timestamp", "00:05:00", "Sym")
   .allBins
   .last("Bid","BidSize","Ask","AskSize")
   .maintainState("Bid","BidSize","Ask","AskSize")
   .execute()

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

import com.illumon.iris.downsampling.Downsampler
downsampledTrades = Downsampler.downsample(db, tradeTable, "Timestamp", "00:05:00", "Symbol")
   .last("Price","Size")
   .sum("Volume=Size")
   .min("Low=Price")
   .max("High=Price")
   .execute()

Calendars Methods

The Calendars class holds the available calendar objects in Deephaven. There are three primary methods to access these calendars:

1. You can specify the calendar by name. (Note:  Use the calendarNames() method to get a list of available calendar names.)
2. You can get the default calendar as specified by the Calendar.default property in your configurations, or you can set the default calendar under Settings.
3. You can use the provided calendars as variables

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

curDayNY = CALENDAR_USYSE.currentDay()

Or equivalently,

from deephaven import *  # Calendars module imported as cals

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

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

In query strings, you can use the default calendar methods statically:

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

Calendar Methods

Many calendar methods are available to help users obtain time and date related information via queries, as shown below.  The methods that take no date parameter act on the current date. For example nextDay(5) is equivalent to nextDay(currentDay(), 5).

See also: Business Calendar Methods

Available Timezones

• TZ_NY(DateTimeZone.forID("America/New_York"))
• TZ_ET(DateTimeZone.forID("America/New_York"))
• TZ_MN(DateTimeZone.forID("America/Chicago"))
• TZ_CT(DateTimeZone.forID("America/Chicago"))
• TZ_MT(DateTimeZone.forID("America/Denver"))
• TZ_PT(DateTimeZone.forID("America/Los_Angeles"))
• TZ_HI(DateTimeZone.forID("Pacific/Honolulu"))
• TZ_BT(DateTimeZone.forID("America/Sao_Paulo"))
• TZ_KR(DateTimeZone.forID("Asia/Seoul"))
• TZ_HK(DateTimeZone.forID("Asia/Hong_Kong"))
• TZ_JP(DateTimeZone.forID("Asia/Tokyo"))
• TZ_AT(DateTimeZone.forID("Canada/Atlantic"))
• TZ_NF(DateTimeZone.forID("Canada/Newfoundland"))
• TZ_AL(DateTimeZone.forID("America/Anchorage"))
• TZ_IN(DateTimeZone.forID("Asia/Kolkata"))
• TZ_CE(DateTimeZone.forID("Europe/Berlin"))
• TZ_SG(DateTimeZone.forID("Asia/Singapore"))
• TZ_LON(DateTimeZone.forID("Europe/London"))
• TZ_MOS(DateTimeZone.forID("Europe/Moscow"))
• TZ_SHG(DateTimeZone.forID("Asia/Shanghai"))
• TZ_CH(DateTimeZone.forID("Europe/Zurich"))
• TZ_NL(DateTimeZone.forID("Europe/Amsterdam"))
• TZ_TW(DateTimeZone.forID("Asia/Taipei"))
• TZ_SYD(DateTimeZone.forID("Australia/Sydney"))
• TZ_UTC(DateTimeZone.UTC)

Business Calendars

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.

Creating a Custom Business Calendar

In addition to the installed business calendars in Deephaven, you can also create your own custom business calendar.

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' 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 (click to expand)

<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 the 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.  The methods that take no date parameter act on the current date, e.g., nextBusinessDay(5) is equivalent to nextBusinessDay(currentDay(), 5).

See also: Calendar Methods.

Business Schedule Methods

A BusinessSchedule is the collection of periods within a 24-hour day when a business is open.  BusinessSchedules 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

BusinessPeriod Methods

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

DBTimeUtils

DBTimeUtils can be used to create, manipulate, and gather information about DBDateTimes, time/durations, and DBPeriods. All of the functions handle null values.