Version: Java (Groovy)

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 in queries. An Instant is accurate to the nearest nanosecond.

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

println 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 dates-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).

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

timeDiff = diffNanos(time2, time1)
println timeDiff

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 uses Java's Period Duration classes.

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.

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

source = emptyTable(10).update("Timestamp = base_time + i * SECOND")
hour_duration = parseDuration("PT1H")
day_period = parsePeriod("P1D")
result1 = source.update("TS2 = plus(Timestamp, hour_duration)")
result2 = source.update("TS2 = plus(Timestamp, day_period)")
result3 = source.update("TS2 = Timestamp + 'PT1H'")
result4 = source.update("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.

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

posPeriod = parsePeriod("P5D")
negPeriod = parsePeriod("P-5D")
posDuration = parseDuration("PT1H1M1S")
negDuration = parseDuration("PT-5H1M5S")


println plus(time1, posPeriod)
println plus(time1, negPeriod)
println plus(time1, posDuration)
println plus(time1, negDuration)

Time zones

Deephaven uses Java's 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.

println timeZone("ET")
println timeZone("PT")
println timeZone("UTC")

By default, Deephaven uses the US East time zone ("America/New_York") as the time zone for database operations. For example, when writing a CSV from a table which includes Instant 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.

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

println time1
println 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
YEAR_AVG
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.

println YEAR_365
println YEAR_AVG
println WEEK
println DAY
println HOUR
println MINUTE
println SECOND

time1 = parseInstant("2020-04-01T12:00:00 ET")
time2 = plus(time1, WEEK)
println time1
println time2

source = timeTable("PT1S")

result = source.update("TS2 = Timestamp + MINUTE")

source
result
Log

Utility methods

There are numerous static 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 import DateTimeUtils statement in the query to import the DateTimeUtils module.

Method	Description
`atMidnight(instant, timeZone)`	Returns a date-time for the requested date-time at midnight in the specified time zone.
`currentClock()`	Returns the clock used to compute the current time.
`dayOfMonth(instant, timeZone)`	Returns the day of the month of the specified date-time and time zone.
`dayOfWeek(instant, timeZone)`	Returns the day of the week of the specified date-time and time zone (1 = Monday, 7 = Sunday).
`dayOfYear(instant, timeZone)`	Returns the day of the year (Julian date) of the specified date-time and time zone.
`diffDays(start, end)`	Calculates the difference between two date-times in days. Both values must be of the same type.
`diffMicros(start, end)`	Calculates the difference between two date-times in microseconds. Both values must be of the same type.
`diffMillis(start, end)`	Calculates the difference between two date-times in milliseconds. Both values must be of the same type.
`diffMinutes(start, end)`	Calculates the difference between two date-times in milliseconds. Both values must be of the same type.
`diffNanos(start, end)`	Returns the difference in nanoseconds between two DateTime values.
`diffSeconds(start, end)`	Calculates the difference between two date-times in seconds. Both values must be of the same type.
`diff_years_365(start, end)`	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(start, start)`	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.
`epochAutoToEpochNanos(epochOffset)`	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.
`epochAutoToInstant(epochOffset)`	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.
`epochAutoToZonedDateTime(epochOffset, timeZone)`	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.
`epochMicros(instant)`	Returns microseconds from the Epoch for a date-time value.
`epochMicrosToInstant(micros)`	Converts a value of microseconds from Epoch in the UTC time zone to a date-time.
`epochMicrosToZonedDateTime(micros, timeZone)`	Converts microseconds from the Epoch to a ZonedDateTime.
`epochMillis(instant)`	Returns milliseconds from the Epoch for a date-time value.
`epochMillisToInstant(millis)`	Converts a value of milliseconds from Epoch in the UTC time zone to a date-time.
`epochMillisToZonedDateTime(millis, timeZone)`	Converts milliseconds from the Epoch to a ZonedDateTime.
`epochNanos(instant)`	Returns nanoseconds from the Epoch for an Instant value.
`epochNanosToInstant(nanos)`	Converts a nanoseconds-since-epoch value to an instant.
`epochNanosToZonedDateTime(nanos, timeZone)`	Converts nanoseconds from the Epoch to a ZonedDateTime.
`epochSeconds(instant)`	Returns seconds from the Epoch for a date-time value.
`epochSecondsToInstant(seconds)`	Converts a seconds-since-epoch value to an instant.
`epochSecondsToZonedDateTime(seconds, timeZone)`	Converts seconds from the Epoch to a ZonedDateTime.
`excelToInstant(excel, timeZone)`	Converts an Excel time represented as a double to an Instant.
`excelToZonedDateTime(excel, timeZone)`	Converts an Excel time represented as a double to a ZonedDateTime.
`formatDate(instant, timeZone)`	Returns a formatted String (date only) for a date-time.
`formatDateTime(instant, timeZone)`	Returns a string date-time representation formatted as “yyyy-MM-ddThh:mm:ss.SSSSSSSSS TZ”.
`formatDurationNanos(nanos)`	Returns a nanosecond duration formatted as a `“[-]PThhh:mm:ss.nnnnnnnnn”` string.
`hourOfDay(instant, timeZone, localTime)`	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).
`isAfter(instant1, instant2)`	Evaluates whether one date-time value is later than a second date-time value.
`isAfterOrEqual(instant1, instant2)`	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.
`isBefore(instant1, instant2)`	Evaluates whether one date0time value is before a second date-time value.
`isBeforeOrEqual(instant1, instant2)`	Evaluates whether one date time value is before or equal to a second date time value. Both values must be of the same type.
`lowerBin(interval, intervalNanos)`	Returns a date-time value, which is at the starting (lower) end of a time range defined by the interval nanoseconds.
`microsOfMilli`	Returns the number of microseconds that have elapsed since the start of the millisecond represented by the provided dateTime in the specified time zone.
`microsOfSecond(instant, timeZone)`	Returns the number of microseconds that have elapsed since the top of the second.
`microsToMillis(micros)`	Converts microseconds to milliseconds.
microsToNanos`	Converts milliseconds to nanoseconds.
`microsToSeconds(micros)`	Converts microseconds to seconds.
`millisOfDay(dateTime, timeZone, localTime)`	Returns the milliseconds since midnight of the specified date-time and time zone.
`millisOfSecond(dateTime, timeZone)`	Returns the milliseconds since the start of the second of the specified date-time and time zone.
`millisToMicros(millis)`	Converts milliseconds to microseconds.
`millisToNanos(millis)`	Converts milliseconds to nanoseconds.
`millisToSeconds(millis)`	Converts milliseconds to seconds.
`minus`	Subtracts one unit of time from another. Multiple overloads.
`minuteOfDay(instant, timeZone, localTime)`	Returns the minutes since midnight of the specified date-time and time zone.
`minuteOfHour(instant, timeZone)`	Returns the minutes since the top of the hour of the specified date-time and time zone.
`monthOfYear(instant, timeZone)`	Returns the month of the year of the specified date-time and time zone (January=1).
`nanosOfDay(instant, timeZone, localTime)`	Returns the nanoseconds since midnight of the specified date-time and time zone.
`nanosOfMilli(instant)`	Returns the number of nanoseconds that have elapsed since the top of the millisecond.
`nanosOfSecond(instant, timeZone)`	Returns the number of nanoseconds that have elapsed since the top of the second.

`nanosToMicros(nanos)`	Returns the microsecond equivalent of the specified nanoseconds.
`nanosToMillis(nanos)`	Returns the millisecond equivalent of the specified nanoseconds.
`nanosToSeconds(nanos)`	Returns the second equivalent of the specified nanoseconds.
`now()`	Provides the current datetime.
`nowMillisResolution()`	Provides the current instant with millisecond resolution according to the current clock.
`nowSystem()`	Provides the current instant with nanosecond resolution according to the system clock.
`nowSystemMillisResolution()`	Provides the current instant with millisecond resolution according to the system clock.
`parseDuration(s)`	Parses the string argument as a duration, which is a unit of time in terms of clock time (24-hour days, hours, minutes, seconds, and nanoseconds).
`parseDurationNanos(s)`	Parses the string argument as a time duration in nanoseconds.
`parseDurationNanosQuiet(s)`	Parses the string argument as a time duration in nanoseconds.
`parseDurationQuiet(s)`	Parses the string argument as a duration, which is a unit of time in terms of clock time (24-hour days, hours, minutes, seconds, and nanoseconds).
`parseEpochNanos(s)`	Parses the string argument as nanoseconds since the Epoch.
`parseEpochNanosQuiet(s)`	Parses the string argument as a nanoseconds since the Epoch.
`parseInstant(s)`	Parses the string argument as an Instant.
`parseInstantQuiet(s)`	Parses the string argument as an Instant.
`parseLocalDate(s)`	Parses the string argument as a local date, which is a date without a time or time zone.
`parseLocalDateQuiet(s)`	Parses the string argument as a local date, which is a date without a time or time zone.
`parseLocalTime(s)`	Parses the string argument as a local time, which is the time that would be read from a clock and does not have a date or timezone.
`parseLocalTimeQuiet(s)`	Parses the string argument as a local time, which is the time that would be read from a clock and does not have a date or timezone.
`parsePeriod(s)`	Parses the string argument as a period, which is a unit of time in terms of calendar time (days, weeks, months, years, etc.).
`parsePeriodQuiet(s)`	Parses the string argument as a period, which is a unit of time in terms of calendar time (days, weeks, months, years, etc.).
`parseTimePrecision(s)`	Returns a ChronoField indicating the level of precision in a time, datetime, or period nanos string.
`parseTimePrecisionQuiet(s)`	Returns a ChronoField indicating the level of precision in a time or datetime string.
`parseTimeZone(s)`	Parses the string argument as a time zone.
`parseTimeZoneQuiet(s)`	Parses the string argument as a time zone.
`parseZonedDateTime(s)`	Parses the string argument as a ZonedDateTime.
`parseZonedDateTimeQuiet(s)`	Parses the string argument as a ZonedDateTime.
`plus`	Adds a time period to a ZonedDateTime or Instant.
`secondOfDay(instant, timeZone, localTime)`	Returns the seconds since midnight of the specified date-time and time zone.
`secondOfMinute(instant, timeZone)`	Returns the seconds since the top of the minute of the specified date-time and time zone.
`secondsToMicros(seconds)`	Converts seconds to microseconds.
`secondsToMillis(seconds)`	Converts seconds to milliseconds.
`secondsToNanos(seconds)`	Converts seconds to nanoseconds.
`setClock(clock)`	Set the clock used to compute the current time.
`timeZone()`	Gets the system default time zone.
`timeZoneAliasAdd(alias, timeZone)`	Adds a new time zone alias.
`timeZoneAliasRm(alias)`	Removes a time zone alias.
`today()`	Provides the current date string according to the current clock and the default time zone.
`toExcelTime(instant, timeZone)`	Converts a date-time to an Excel time represented as a double.
`toInstant(dateTime)`	Converts a date-time string to an instant.
`toLocalDate(instant, timeZone)`	Converts a date-time to a local date.
`toLocalTime(instant, timeZone)`	Converts a date-time to a LocalTime.
`toZonedDateTime(instant, timeZone)`	Converts a date-time string to a Zoned date-time.
`upperBin(instant, intervalNanos)`	Returns the upper bin; bins the date-times according to the `intervalNanos`.
`year(instant, timeZone)`	Returns the year of the specified date-time and time zone.
`yearOfCentury(instant, timeZone)`	Returns the year of the century of the specified date time and time zone.

Examples

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

note

The examples in theis section depend on the events table.

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

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

events = newTable(
   instantCol("EventDateTime", time1, time2, time3, time4),
   stringCol("Level", "Info", "Error", "Warning", "Info"),
   stringCol("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 ’.

eventsAfterTen = events.where("EventDateTime > '2020-04-01T10:00 ET'")

eventsAfterTen

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.

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

eventsMiddleDay1

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

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

eventsMiddleDay2

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.

baseTime = parseInstant("2020-04-01T10:00:00 ET")
eventsMiddleDay3 = events.where("EventDateTime >= baseTime && EventDateTime <= (baseTime + 6 * HOUR)")

eventsMiddleDay3

The following example creates a table with a new column consisting of the hour of the day in which the events occurred, in the UTC time zone.

tz = timeZone("UTC")

eventsWithUTCHour = events.update("HourOfDayUTC = hourOfDay(EventDateTime, tz, false)")

eventsWithUTCHour

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

instant = parseInstant("2020-04-01T12:00:00 ET")

tz = timeZone("ET")

midnight = atMidnight(instant, tz)

eventsWithOffset1 = events.update("OffsetMillis = (EventDateTime - midnight) / 1_000_000")

eventsWithOffset2 = events.update("OffsetMillis = (EventDateTime - atMidnight(EventDateTime, tz)) / 1_000_000")

eventsWithOffset1
eventsWithOffset2

Date and time measurement​

Specific date-times​

Periods and Durations​

Time zones​

Predefined date-time variables​

Utility methods​

Examples​

Related documentation​

Date and time measurement

Specific date-times

Periods and Durations

Time zones

Predefined date-time variables

Utility methods

Examples

Related documentation