Time

In addition to the code snippets included below, all of the Groovy and Python code for the recipes below can be downloaded as premade Deephaven Notebooks. To learn more about using Deephaven Notebooks, please refer to the Notebook section of the User Manual.

Download the Query Cookbook Notebooks

Filtering By Time

Step 1: Pull trades from a certain date and remove some columns.

trades = db.t("LearnDeephaven", "StockTrades")
.where("Date=`2017-08-25`")
.view("Sym", "Last", "ExchangeTimestamp")

Step 2: Filter table to present only trades occurring during USNYSE business time.

usnyseTime = trades.where("CALENDAR_USNYSE.isBusinessTime(ExchangeTimestamp)")

Step 3: Filter the table to present only those trades that occurred after noon.

afternoon = usnyseTime.where("ExchangeTimestamp > '2017-08-25T12:00 NY'")

The format of a date time string is: yyyy-mm-ddThh:mm:ss.nanoseconds TZ, with TZ representing time zone. Also, the number of seconds do not need to be specified in the Deephaven Query language; they are optional. Last, backticks are not used when writing times within a query; rather, single quotes are used. This is the main way you will be dealing with times in Deephaven.

Complete Code Blocks for the Filtering By Time Recipe

trades = db.t("LearnDeephaven", "StockTrades")
     .where("Date=`2017-08-25`")
     .view("Sym", "Last", "ExchangeTimestamp")
usnyseTime = trades.where("CALENDAR_USNYSE.isBusinessTime(ExchangeTimestamp)")
afternoon = usnyseTime.where("ExchangeTimestamp > '2017-08-25T12:00 NY'")

# Initial import

from deephaven import *

print("Provides:\n"
      "\tdeephaven.Calendars as cals\n"
      "\tdeephaven.ComboAggregateFactory as caf\n"
      "\tdeephaven.DBTimeUtils as dbtu\n"
      "\tdeephaven.Plot as plt\n"
      "\tdeephaven.Plot.figure_wrapper as figw\n"
      "\tdeephaven.QueryScope as qs\n"
      "\tdeephaven.TableManagementTools as tmt\n"
      "\tdeephaven.TableTools as ttools\n"
      "See print(sorted(dir())) for the full namespace contents.")


# Filtering By Time

usnyse = cals.calendar("USNYSE")
trades = db.t("LearnDeephaven", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .view("Sym", "Last", "ExchangeTimestamp")
usnyseTime = trades.where("usnyse.isBusinessTime(ExchangeTimestamp)")
afternoon = usnyseTime.where("ExchangeTimestamp > '2017-08-25T12:00 NY'")

DateTime Strings

Step 1: Pull trades from a certain date and remove some columns.

trades = db.t("LearnDeephaven", "StockTrades")
.where("Date=`2017-08-25`")
.view("Sym", "Last", "ExchangeTimestamp")

Step 2: Convert date time strings to DateTimes

There is a difference between a date time string and an actual DateTime. For example, let's repeat step 3 of Filtering By Time except we'll compare times with a variable:

time = "2017-08-25T12:00 NY"
afternoon = trades.where("ExchangeTimestamp > time")

This query will cause an error. Even though ExchangeTimestamp values are similar to time, the actual value types are not equal. Specifically, ExchangeTimestamp in trades holds a DateTime value type, whereas the time variable simply holds a string. However, you can turn the string into a date time value with the convertDateTime function.

time = convertDateTime("2017-08-25T12:00 NY")
afternoon = trades.where("ExchangeTimestamp > time")

The time variable in the query above now holds a DateTime value type instead of a string. Therefore the query generating the afternoon table can correctly show only those values after noon.

Complete Code Blocks for the DateTime Strings Recipe

trades = db.t("LearnDeephaven", "StockTrades")
     .where("Date=`2017-08-25`")
     .view("Sym", "Last", "ExchangeTimestamp")

time = convertDateTime("2017-08-25T12:00 NY")

afternoon = trades.where("ExchangeTimestamp > time")

# Initial import

from deephaven import *

trades = db.t("LearnDeephaven", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .view("Sym", "Last", "ExchangeTimestamp")
time = dbtu.convertDateTime("2017-08-25T12:00 NY")
afternoon = trades.where("ExchangeTimestamp > time")

Adjusting Time Mathematically

Step 1: Pull trades from a certain date and remove some columns.

trades = db.t("LearnDeephaven", "StockTrades")
.where("Date=`2017-08-25`")
.view("Sym", "Last", "ExchangeTimestamp")

Step 2: Add a new column to hold a date time one hour later than that shown in `ExchangeTimestamp`.

The following three queries return identical tables with a new column called HourAfter that adds one hour to the timestamp for each row.

hourAfter1 = trades.updateView("HourAfter = ExchangeTimestamp + '01:00'")
hourAfter2 = trades.updateView("HourAfter = ExchangeTimestamp + 'T1h'")
hourAfter3 = trades.updateView("HourAfter = ExchangeTimestamp + HOUR")

hourAfter1 demonstrates how to add a time duration, following the format hh:mm:ss.nanoseconds.
hourAfter2 demonstrates how to add a time period, following the format #y#m#dT#h#m#s.
hourAfter3 demonstrates how to add nanoseconds as constants; e.g. MINUTE, HOUR, etc.

Step 3: Calculate the difference in time between an event and the current time

The following query will calculate the difference (in days) between the time of each trade and the current time.

timeDifference = trades.updateView("Difference=diffDay(ExchangeTimestamp, currentTime())")

Complete Code Blocks for the Adjusting Time Mathematically Recipe

trades = db.t("LearnDeephaven", "StockTrades")
     .where("Date=`2017-08-25`")
     .view("Sym", "Last", "ExchangeTimestamp")

hourAfter1 = trades.updateView("HourAfter = ExchangeTimestamp + '01:00'")
hourAfter2 = trades.updateView("HourAfter = ExchangeTimestamp + 'T1h'")
hourAfter3 = trades.updateView("HourAfter = ExchangeTimestamp + HOUR")

timeDifference = trades.updateView("Difference=diffDay(ExchangeTimestamp, currentTime())")

# Initial import

from deephaven import *

# Adjusting Time Mathematically

trades = db.t("LearnDeephaven", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .view("Sym", "Last", "ExchangeTimestamp")
hourAfter1 = trades.updateView("HourAfter = ExchangeTimestamp + '01:00'")
hourAfter2 = trades.updateView("HourAfter = ExchangeTimestamp + 'T1h'")
hourAfter3 = trades.updateView("HourAfter = ExchangeTimestamp + HOUR")
timeDifference = trades.updateView("Difference=diffDay(ExchangeTimestamp, currentTime())")

Last Updated: 28 February 2020 12:20 -05:00 UTC Deephaven v.1.20200121 (See other versions)

Time

Filtering By Time

Step 1: Pull trades from a certain date and remove some columns.

Step 2: Filter table to present only trades occurring during USNYSE business time.

Step 3: Filter the table to present only those trades that occurred after noon.

Complete Code Blocks for the Filtering By Time Recipe

DateTime Strings

Step 1: Pull trades from a certain date and remove some columns.

Step 2: Convert date time strings to DateTimes

Complete Code Blocks for the DateTime Strings Recipe

Adjusting Time Mathematically

Step 1: Pull trades from a certain date and remove some columns.

Step 2: Add a new column to hold a date time one hour later than that shown in ExchangeTimestamp.

Step 3: Calculate the difference in time between an event and the current time

Complete Code Blocks for the Adjusting Time Mathematically Recipe

Step 2: Add a new column to hold a date time one hour later than that shown in `ExchangeTimestamp`.