Temporal Examples

Click here to download this example set in a Deephaven Notebook (Python)

Click here to download this example set in a Deephaven Notebook (Groovy)

Filter the table to return trade data for only one specific date

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

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). See also: calendar.currentDay()

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

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

Using java.lang.String methods (note: Date is a Java String object) to filter the table 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`)")

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

Filter the table using comparators to show trades that occurred in the month of August 2017

trades08Comparator = db.t("LearnDeephaven","StockTrades").where("Date > `2017-08` && Date < `2017-09`")

trades08Comparator = db.t("LearnDeephaven","StockTrades").where("Date > `2017-08` && Date < `2017-09`")

Filter the table to show trades that occurred in the month of August 2017

Note: This is equivalent to the previous example. However, this one uses the inRange() method to determine the date range for the filter.

trades08InRange = db.t("LearnDeephaven","StockTrades").where("inRange(Date, `2017-08-01`, `2017-09-01`)")

trades08InRange = db.t("LearnDeephaven","StockTrades").where("inRange(Date, `2017-08-01`, `2017-09-01`)")

Filter the table to show trades that occurred after 8am on August 21 (NY time)

Note: To construct DBDateTimes in the query language, use the single quote ('). Comparison operators on DBDateTimes are supported in the query language strings.

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

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

Filter the table using comparators to show trades that occurred after 8am and before 5pm on August 21 (NY time)

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

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

Filter the table to show trades that occurred after 8am and before 5pm on August 21 (NY time)

Note: This is equivalent to the previous example. However, this one uses the inRange() method method to determine the date range for the filter.

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

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

Filter the table to show trades that occurred between a user-determined variable called baseTime (e.g., 9:35 am) through baseTime plus one hour (e.g., 10:35 am).

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

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

//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.
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)")

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

This query transforms DBDateTimes to equivalent nanos from Epoch

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

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

The following query uses the default calendar in Deephaven (USNYSE) to filter the table to show only those trades that occurred during actual business hours, e.g., USNYSE hours were 9:30am - 4pm on Aug 21, 2017

trades08BusinessHours4 = trades08.where("isBusinessTime(Timestamp)")

trades08BusinessHours4 = trades08.where("isBusinessTime(Timestamp)")

The next query uses a calendar for the next three days

Note that caching the nextDay(3) manually will make this significantly faster.

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

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

The following query uses the inRange() and the currentDay() methods to show only those trades that occurred on the current day and the next three days (inclusive).

trades08BusinessHours5 = trades08.where("inRange(Date, current_day, current_day_plus3)")

trades08BusinessHours5 = trades08.where("inRange(Date, currentDay, currentDayPlus3)")

The following two queries are equivalent and filter the table to show only those trades that occurred on the first day of the week (Monday).

trades08Mondays1 = trades08.where("dayOfWeek(Date).getValue() = 1")

trades08Mondays1 = trades08.where("dayOfWeek(Date).getValue() = 1")

trades08Mondays2 = trades08.where("dayOfWeek(Date) = java.time.DayOfWeek.MONDAY")

trades08Mondays2 = trades08.where("dayOfWeek(Date) = java.time.DayOfWeek.MONDAY")

Creates a ticking table which gets a new row every second, which is useful for table snapshots

tt = db.timeTable('00:00:01')

tt = db.timeTable('00:00:01')

Last Updated: 16 February 2021 18:07 -04:00 UTC Deephaven v.1.20200928 (See other versions)