Working with time in DataPrime

Timestamp expressions

DataPrime supports both ISO8601 and Unix timestamp formats. Timestamp expressions are prefixed by @ and always return a timestamp.

Type	Format	Examples
Number timestamp literals	Seconds (10 digits)	@1234567890
String timestamp literals	ISO 8601 date/time	@'2023-08-07T19:06:42'

You can specify timestamps in various formats for more precise time-based queries.

source logs between @'2024-08-01T00:00:00' and @'2024-08-01T02:00:00' | count

Using the current time in a query

A special timestamp literal to get the current time is the following: @'now'. This is a timestamp literal like any other, and can be amended using expressions. For example, to get 24 hours ago:

@'now' - 24h

Handling custom time ranges

Custom time ranges allow you to focus on specific periods of interest.

Querying data within a specific time range

Filter data by specifying exact start and end timestamps using between.

source logs between @'2024-08-15T00:00:00' and @'2024-08-15T23:59:59'

Using relative time ranges

DataPrime also supports relative time ranges, like last hour or last day.

source logs last 1h
source logs last 1d

Timestamp math and interval literals

DataPrime allows arithmetic operations on timestamps using intervals. This capability is essential for comparing timestamps, calculating time differences, and shifting time ranges.

timestamp + interval
timestamp - interval
interval + interval
interval - interval

Example:

source logs | filter $m.timestamp - 5m > some_other_timestamp

Interval literals

Interval literals represent specific spans of time and can be used in timestamp arithmetic.

Examples:

• 5 minutes: 5m
• 1 hour and 30 minutes: 1h30m
• 2 days: 2d

Select events around a specific time with around

You can select events around a specific time with the around keyword.

Example:

source logs around @'2021-01-01T00:00:00Z' interval 1h

Select events for the last 24 hours with between

Retrieve events between now and the last 24 hours

Example:

source logs between @'now' - 24h and @'now'

Select events from the last <interval> with last

Retrieve events from the last <interval> using the last keyword.

Examples:

source logs last 2h | count
source logs last 10h5m30s

Grouping data by time intervals with groupby

You can aggregate data by specific time intervals using the groupby operator, ideal for time-series analysis.

Example:

source logs | groupby $m.timestamp / 1h aggregate count() as hourly_count

Time shifting

Time shifting allows comparison of data across different time periods.

Example:

source logs timeshifted -3d | count

Comparing time periods

By using time shifting, you can compare data from different periods, like today’s metrics with yesterday's.

Example:

source logs between now() - 7d and now() | groupby $m.timestamp / 1d as ts aggregate sum(requests) as today_requests
| join (
    source logs between now() - 8d and now() - 1d | groupby $m.timestamp / 1d as ts aggregate sum(requests) as yesterday_requests
) on left=>ts == right=>ts + 1d into comparison
| create percentage_difference from (comparison.yesterday_requests / today_requests) * 100 - 100

Advanced time-based operations

Sliding windows for real-time analysis

Sliding windows allow you to calculate metrics over a relative time window, like the last 5 minutes.

Example:

source logs | groupby $m.timestamp / 5m aggregate avg(response_time) as rolling_avg_response_time

Aggregating data across time intervals

Aggregating data over time intervals is key for generating summaries over hours, days, or weeks.

Example:

source logs | groupby $m.timestamp / 1h aggregate count() as hourly_event_count

Combining multiple time aggregations

In complex analyses, you can aggregate data across multiple time intervals by chaining groupby statements.

Example:

source logs | groupby $m.timestamp / 1h agg count() as hourly_event_count
| groupby $m.timestamp / 1d agg avg(hourly_event_count) as daily_avg_count