Version: Python

How to work with date-times

This guide will show you how to work with date-times. This is particularly important for time-series use cases, which are very common in Deephaven queries.

Date and time measurement

There are several possible ways to describe and measure dates and times.

Specific date-times

Deephaven uses java.time.Instant to represent specific points in time to the nearest nanosecond.

To see the current date and time on your system, run the following:

from deephaven.time import now

print(now())

This will print out a date and time in the default format, such as 2021-09-09T11:58:41.041000000 ET. The default format consists of the following fields:

yyyy-MM-ddThh:mm:ss.ffffff TZ

yyyy - the year
MM - the month
dd - the day
T - the separator between the date and time
hh - the hour of the day
mm - the minute of the hour
ss - the second of the minute
ffffff - the fraction of a second
TZ - the time zone

Deephaven stores date-times using the java.time.Instant class. Internally, this stores the date-time as a signed 64-bit long, which contains the number of nanoseconds since the Unix epoch (January 1, 1970, 00:00:00 GMT). You can create these directly (like in the code above) and use dates and times directly in the query language, including adding and subtracting them.

The following example shows the creation of two specific points in time, time1 and time2, exactly one year apart from each other. We then calculate the difference between these two date/time instances, resulting in 31,536,000,000,000,000 nanos (there are 31,536,000 seconds in a year).

from deephaven.time import parse_instant, diff_nanos

time1 = parse_instant("2020-08-01T12:00:00 ET")
time2 = parse_instant("2021-08-01T12:00:00 ET")

time_diff = diff_nanos(time1, time2)
print(time_diff)

Local dates and times

A local date is a date without a time or time zone. A local time is the time that would be read from a clock and does not have a date or time zone.

Periods and Durations

Periods and durations represent spans of time that can be either positive or negative. A period represents a span of time that is greater than one day - lengths of time one would normally count on a calendar. A duration represents a span of time that is less than one day in length - spans of time one would normally count on a clock (but accurate down to the millisecond!). Deephaven implements periods using the Period class, and durations using the Duration class.

Durations are prefixed by a PT, whereas periods are prefixed by the letter P.

The following query demonstrates several query operations using periods and durations.

note

In the example below, we've set the default format for Timestamp columns to YYYY-MM-DDThh:mm:ss.fff. See How to set date-time format to learn how.

from deephaven.time import parse_instant, parse_duration, parse_period
from deephaven import empty_table

base_time = parse_instant("2023-01-01T00:00:00 UTC")

source = empty_table(10).update(["Timestamp = base_time + i * SECOND"])
hour_duration = parse_duration("PT1H")
day_period = parse_period("P1D")
result1 = source.update(formulas=["TS2 = plus(Timestamp, hour_duration)"])
result2 = source.update(formulas=["TS2 = plus(Timestamp, day_period)"])
result3 = source.update(formulas=["TS2 = Timestamp + 'PT1H'"])
result4 = source.update(formulas=["TS2 = Timestamp + 'P-1D'"])

source
result1
result2
result3
result4

The above example creates a source table from an Instant called base_time. Each row of the source table is one second after the previous. A one hour duration and one day period are then instantiated. From there, four resultant tables are derived from source:

result1 uses the query language's built-in plus method to add hour_duration to Timestamp, so the TS2 column contains timestamps one hour after those in the Timestamp column.
result2 uses the same built-in plus method, but adds day_period instead. Thus, TS2 contains timestamps one day after those in the Timestamp column.
result3 uses the overloaded + operator to add a one hour duration to Timestamp again. Thus, TS2 looks identical to that in the result1 table.
result4 uses the overloaded + operator to add a negative day-long period to Timestamp. Thus, TS2 looks identical to that in the result2 table.

Durations use the following suffixes for different units of time:

-: Negative
H: Hour
M: Minute
S: Second

Periods use the following suffixes for different units of time:

-: Negative
Y: Year
M: Month
D: Day

For example, a duration of PT1H1M-1S means one second short of one hour and one minute. Equivalently, a period of P1Y1M-1D means one day short of one year and one month.

The following example creates an instant, then adds a positive and negative period and duration to it.

from deephaven.time import parse_instant, parse_period, parse_duration, plus_period

time1 = parse_instant("2020-04-01T12:00:00 ET")

pos_period = parse_period("P5D")
neg_period = parse_period("P-5D")
pos_duration = parse_duration("PT1H1M1S")
neg_duration = parse_duration("PT-5H1M5S")

print(plus_period(time1, pos_period))
print(plus_period(time1, neg_period))
print(plus_period(time1, pos_duration))
print(plus_period(time1, neg_duration))

Time zones

Deephaven uses its own TimeZone class to store time zone information. Time zones in instants use abbreviations, such as ET for US Eastern time, UTC or Z for coordinated universal time (UTC), etc.

from deephaven.time import time_zone

print(time_zone("ET"))
print(time_zone("PT"))
print(time_zone("UTC"))

By default, Deephaven uses the US Eastern time zone ("America/New_York") as the time zone for database operations. For example, when writing a CSV from a table which includes DateTime columns, the New York time zone will be used unless a different time zone is passed in to the write_csv call.

The following example prints a time in the Denver time zone, and in the New York time zone. The printed values end with Z (UTC), so the two hour difference between the two time zones is apparent.

from deephaven.time import parse_instant

time1 = parse_instant("2021-01-01T01:00 ET")
time2 = parse_instant("2021-01-01T01:00 MT")

print(time1)
print(time2)

Predefined date-time variables

Deephaven provides a number of predefined date/time variables which represent specified time periods in nanoseconds. These can be used in formulas.

Predefined date-time variables are:

YEAR_365 - Years are defined in terms of 365 day years.
YEAR_AVG - Years are defined in terms of 365.2425 day years.
WEEK
DAY
HOUR
MINUTE
SECOND

The following example shows these variables and how to use them, both in a simple calculation and when manipulating a table. Notice that the timestamps in TS2 are one minute after the timestamps in the original table. A user will not typically call plus directly; the Deephaven Query Language will do that automatically when times are summed, as in the source/result part of the example.

from deephaven import time as dhtu
from deephaven import empty_table

print(dhtu.YEAR_AVG)
print(dhtu.YEAR_365)
print(dhtu.WEEK)
print(dhtu.DAY)
print(dhtu.HOUR)
print(dhtu.MINUTE)
print(dhtu.SECOND)

time1 = dhtu.parse_instant("2020-04-01T12:00:00 PT")
time2 = dhtu.plus_period(time1, dhtu.parse_period("P1W"))
print(time1)
print(time2)
source = empty_table(10).update(["Timestamp = time1 + i * MINUTE"])
result = source.update(formulas=["TS2 = Timestamp + MINUTE"])

source
result

Utility methods

There are numerous methods that can be used to create, manipulate, and gather information about date-times, periods, and time/durations. 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 include the from deephaven.time statement in the query to import the module.

note

Specific functions from Time, such as now, can be imported into Python and then referenced directly. For example:

from deephaven.time import now

Method	Description
`at_midnight(dt, tz)`	Returns a date-time for the requested date-time at midnight in the specified time zone.
`day_of_month(dt, tz)`	Returns an int value of the day of the month for a date-time and specified time zone.
`day_of_week(dt, tz)`	Returns an int value of the day of the week for a date-time in the specified time zone.
`day_of_year(dt, tz)`	Returns an int value of the day of the year for a date-time in the specified time zone.
`diff_days(dt1, dt2)`	Calculates the difference between two date-times in days. Both values must be of the same type.
`diff_minutes(dt1, dt2)`	Calculates the difference between two date-times in milliseconds. Both values must be of the same type.
`diff_micros(dt1, dt2)`	Calculates the difference between two date-times in microseconds. Both values must be of the same type.
`diff_millis(dt1, dt2)`	Calculates the difference between two date-times in milliseconds. Both values must be of the same type.
`diff_minutes(dt1, dt2)`	Calculates the difference between two date-times in milliseconds. Both values must be of the same type.
`diff_nanos(dt1, dt2)`	Calculates the difference between two date-times in nanoseconds. Both values must be of the same type.
`diff_seconds(dt1, dt2)`	Calculates the difference between two date-times in seconds. Both values must be of the same type.
`diff_years_365(dt1, dt2)`	Calculates the difference between two date-times in years. Years are defined in terms of 365 day years. Both values must be of the same type.
`diff_years_avg(dt1, dt2)`	Calculates the difference between two date-times in years. Years are defined in terms of 365.2425 day years. Both values must be of the same type.
`epoch_auto_to_epoch_nanos(epoch_offset)`	Converts an offset from the Epoch to a nanoseconds from the Epoch. The offset can be in milliseconds, microseconds, or nanoseconds. Expected date ranges are used to infer the units for the offset.
`epoch_auto_to_instant(epoch_offset)`	Converts an offset from the Epoch to an Instant. The offset can be in milliseconds, microseconds, or nanoseconds. Expected date ranges are used to infer the units for the offset.
`epoch_auto_to_zdt(epoch_offset)`	Converts an offset from the Epoch to a ZonedDateTime. The offset can be in milliseconds, microseconds, or nanoseconds. Expected date ranges are used to infer the units for the offset.
`epoch_micros(dt)`	Returns microseconds from the Epoch for a date-time value.
`epoch_micros_to_instant(us)`	Converts a value of microseconds from Epoch in the UTC time zone to a date-time.
`epoch_micros_to_zdt(us)`	Converts microseconds from the Epoch to a ZonedDateTime.
`epoch_millis(dt)`	Returns milliseconds from the Epoch for a date-time value.
`epoch_millis_to_instant(ms)`	Converts a value of milliseconds from Epoch in the UTC time zone to a date-time.
`epoch_millis_to_zdt(ms)`	Converts milliseconds from the Epoch to a ZonedDateTime.
`epoch_nanos_to_instant(ns)`	Converts a nanoseconds-since-epoch value to an instant.
`epoch_nanos(dt)`	Returns nanoseconds from the Epoch for a date-time value.
`epoch_nanos_to_zdt(ns)`	Converts nanoseconds from the Epoch to a ZonedDateTime.
`epoch_seconds(dt)`	Returns seconds from the Epoch for a date-time value.
`epoch_seconds_to_instant(s)`	Converts a seconds-since-epoch value to an instant.
`epoch_seconds_to_zdt(s)`	Converts seconds from the Epoch to a ZonedDateTime.
`excel_to_instant(float, tz)`	Converts an Excel time represented as a double to an Instant.

`excel_to_zdt(float, tz)`	Converts an Excel time represented as a double to a ZonedDateTime.
`format_date(dt, tz)`	Returns a string date representation of a date-time interpreted for a specified time zone formatted as “yyy-MM-dd”.
`format_datetime(dt, tz)`	Returns a string date-time representation formatted as “yyyy-MM-ddThh:mm:ss.SSSSSSSSS TZ”.
`format_duration_nanos(ns)`	Returns a nanosecond duration formatted as a `“[-]PThhh:mm:ss.nnnnnnnnn”` string.
`hour_of_day(dt, tz)`	Returns the hour of the day for a date-time in the specified time zone. The hour is on a 24 hour clock (0 - 23).
`is_after(dt1, dt2)`	Evaluates whether one date-time value is later than a second date-time value.
`is_after_or_equal(dt1, dt2)`	Evaluates whether one date-time value is later than or equal to a second date-time value. Both values must be of the same type.
`is_before(dt1, dt2)`	Evaluates whether one date0time value is before a second date-time value.
`is_before_or_equal(dt1, dt2)`	Evaluates whether one date time value is before or equal to a second date time value. Both values must be of the same type.
`lower_bin(dt, interval, offset)`	Returns a date-time value, which is at the starting (lower) end of a time range defined by the interval nanoseconds.
`make_instant(dt, tz)`	Makes an Instant.
`make_zdt(dt, tz)`	Makes an Zoned date-time.
`millis_of_day(dt, tz)`	Returns the number of milliseconds since midnight for a date-time in the specified time zone.
`millis_of_second(dt, tz)`	Returns the number of milliseconds since the top of the second for a date-time in the specified time zone.
`micros_of_milli(dt)`	Returns the number of microseconds that have elapsed since the top of the millisecond.
`micros_of_second(dt, tz)`	Returns the number of microseconds that have elapsed since the top of the second for a date-time in the specified time zone.

`micros_to_nanos(ms)`	Converts microseconds to nanoseconds.
`micros_to_millis(us)`	Converts microseconds to milliseconds.
`micros_to_seconds(us)`	Converts microseconds to seconds.
`millis_to_micros(ms)`	Converts milliseconds to microseconds.
`millis_to_nanos(ms)`	Converts milliseconds to nanoseconds.
`millis_to_seconds(ms)`	Converts milliseconds to seconds.
`minus_period(dt, period)`	Subtracts a period from a date-time.
`minute_of_day(dt, tz)`	Returns the minutes since midnight of the specified date-time and time zone.
`minute_of_hour(dt, tz)`	Returns the minutes since the top of the hour of the specified date-time and time zone.
`month_of_year(dt, tz)`	Returns the month of the year of the specified date-time and time zone (January=1).
`nanos_of_day(dt, tz)`	Returns the nanoseconds since midnight of the specified date-time and time zone.
`nanos_of_milli(dt)`	Returns the number of nanoseconds that have elapsed since the top of the millisecond.
`nanos_of_second(dt, tz)`	Returns the number of nanoseconds that have elapsed since the top of the second.

`nanos_to_micros(ns)`	Returns the microsecond equivalent of the specified nanoseconds.
`nanos_to_seconds(ns)`	Returns the second equivalent of the specified nanoseconds.
`nanos_to_millis(ns)`	Returns the millisecond equivalent of the specified nanoseconds.
`now()`	Provides the current datetime.
`parse_duration(str)`	Converts a string to a duration.
`parse_duration_nanos(s, quiet)`	Converts a duration string to nanoseconds.
`parse_epoch_nanos(str)`	Parses the string argument as nanoseconds since the Epoch.

`parse_instant(str)`	Returns a String as a date-time.
`parse_local_date(str)`	Converts a string to a local date.
`parse_local_time(str)`	Converts a string to a local time.
`parse_period(str)`	Converts a string to a period.
`parse_time_precision(str)`	Returns a string indicating the level of precision in a time, date-time, or period nanoseconds string. (e.g., ‘SecondOfMinute’).
`parse_time_zone(str)`	Converts a string to a time zone.
`parse_zdt(str)`	Converts a string to a ZonedDateTime.
`plus_period(dt, period)`	Returns the sum of a date-time and a period (Note: you can use `dateTime+period` in the query language).
`second_of_day(dt, tz)`	Returns the seconds since midnight of the specified date-time and time zone.
`second_of_minute(dt, tz)`	Returns the seconds since the top of the minute of the specified date-time and time zone.
`seconds_to_micros(s)`	Converts seconds to microseconds.
`seconds_to_millis(s)`	Converts seconds to milliseconds.
`seconds_to_nanos(s)`	Converts seconds to nanoseconds.
`to_excel_time(dt, tz)`	Converts a date-time to an Excel time represented as a double.
`to_instant(str)`	Converts a date-time string to an instant.
`to_local_date(dt)`	Converts a date-time to a local date.
`to_local_time(dt)`	Converts a date-time to a LocalTime.
`to_zdt(str)`	Converts a date-time string to a Zoned date-time.
`upper_bin(dt, intervalNanos)`	Returns the upper bin; bins the date-times according to the `intervalNanos`.
`year(dt, timeZone)`	Returns the year of the specified date-time and time zone.
`year_of_century(dt, tz)`	Returns the year of the century of the specified date time and time zone.

See the date-time reference section for more information.

Examples

In this section, we create a table to contain events, then demonstrate the date and time methods described above.

note

The examples in this section depend upon the events table.

First, we create a simple table with four rows. Note: our times are all based in NY.

from deephaven import new_table
from deephaven.column import string_col, datetime_col
from deephaven.time import parse_instant

time1 = parse_instant("2020-04-01T09:00:00 ET")
time2 = parse_instant("2020-04-01T10:28:32 ET")
time3 = parse_instant("2020-04-01T12:00:00 ET")
time4 = parse_instant("2020-04-01T16:59:59 ET")

events = new_table([
   datetime_col("EventDateTime", [time1, time2, time3, time4]),
   string_col("Level", ["Info", "Error", "Warning", "Info"]),
   string_col("Event", ["System starting", "Something bad happened", "Invalid login", "System stopping"]),
])

events

The following query returns all events after 10AM 2020-04-01. Note that comparison operators on date-times are supported in query language strings. In order to use a comparison operator on a date-time string, the date-time string must be wrapped in single quotes ’.

events_after_ten = events.where(filters=["EventDateTime > '2020-04-01T10:00 ET'"])

events_after_ten

note

To construct date-times in DQL, use the single quote.

The following example returns all events between 10AM - 4PM on 2020-04-01, using a formula within the query string.

events_middle_day_1 = events.where(filters=["EventDateTime >= '2020-04-01T10:00 ET' && EventDateTime <= '2020-04-01T16:00 ET'"])

events_middle_day_1

The following example returns all dates between 10AM - 4PM on 2020-04-01, using the inRange method.

events_middle_day_2 = events.where(filters=["inRange(EventDateTime, '2020-04-01T10:00 ET', '2020-04-01T16:00 ET')"])

events_middle_day_2

Date-times support simple operands as well. The following example filters the table to a 10AM starting time, with an ending time of that plus six hours.

base_time = parse_instant("2020-04-01T10:00:00 ET")
events_middle_day_3 = events.where(filters=["EventDateTime >= '2020-04-01T10:00 ET' && EventDateTime <= (base_time + 6 * HOUR)"])

events_middle_day_3

The following example recreates the table from our earlier example, and then creates a table with a new column consisting of the hour of the day in which the events occurred, in the UTC time zone.

from deephaven import new_table
from deephaven.column import string_col, datetime_col
from deephaven.time import parse_instant, time_zone, hour_of_day

time1 = parse_instant("2020-04-01T09:00:00 ET")
time2 = parse_instant("2020-04-01T10:28:32 ET")
time3 = parse_instant("2020-04-01T12:00:00 ET")
time4 = parse_instant("2020-04-01T16:59:59 ET")

events = new_table([
   datetime_col("EventDateTime", [time1, time2, time3, time4]),
   string_col("Level", ["Info", "Error", "Warning", "Info"]),
   string_col("Event", ["System starting", "Something bad happened", "Invalid login", "System stopping"]),
])

tz = time_zone("UTC")

events_with_UTC_hour = events.update(formulas=["HourOfDayUTC = hour_of_day(EventDateTime, tz)"])

events
events_with_UTC_hour

The following example creates a table showing the event's offset in millis after midnight New York time, first using a variable, then by performing the midnight calculation directly in the formula.

from deephaven.time import time_zone, parse_instant, at_midnight

et = time_zone("ET")

midnight = at_midnight(parse_instant("2020-04-01T12:00:00 ET"), time_zone("ET"))
events_with_offset_1 = events.update(formulas=["OffsetMillis = (EventDateTime - midnight) / 1_000_000"])
events_with_offset_2 = events.update(formulas=["OffsetMillis = (EventDateTime - atMidnight(EventDateTime, et)) / 1_000_000"])

events_with_offset_1
events_with_offset_2

Date and time measurement​

Specific date-times​

Local dates and times​

Periods and Durations​

Time zones​

Predefined date-time variables​

Utility methods​

Examples​

Related documentation​

Date and time measurement

Specific date-times

Local dates and times

Periods and Durations

Time zones

Predefined date-time variables

Utility methods

Examples

Related documentation