Time

Time is central to event analytics. In Hamelin, you write time the way you think about it—1hr, 30min, or yesterday. The language supports several ways to work with time: intervals for durations, absolute timestamps for specific moments, time truncation for grouping, and ranges for time spans.

Time intervals

You use time intervals to express durations—how long something takes or how far back to look in your data.

Basic interval syntax

Create time intervals by writing a number directly followed by a time unit, with no spaces. Use these anywhere you need to specify how long something takes or how far back to look:

# Time intervals - number + unit
1sec        # 1 second
30sec       # 30 seconds
1min        # 1 minute
15min       # 15 minutes
1hr         # 1 hour
2hr         # 2 hours
1d          # 1 day
7d          # 7 days
1w          # 1 week
2w          # 2 weeks
1mon        # 1 month
6mon        # 6 months
1yr         # 1 year

Supported time units

Unit	Abbreviations	Examples
Seconds	s, sec, secs, second, seconds	30s, 45sec
Minutes	m, min, mins, minute, minutes	5m, 15min
Hours	h, hr, hrs, hour, hours	1h, 2hr
Days	d, day, days	1d, 30days
Weeks	w, week, weeks	1w, 2weeks
Months	mon, month, months	1mon, 3months
Years	y, yr, yrs, year, years	1y, 2yrs

Using intervals in variables

Store commonly used time intervals in variables to make your queries more readable and maintainable:

# Store intervals in variables for reuse
DEF time_constants =
  SET short_window = 5min,
      daily_retention = 30d,
      investigation_period = 2hr,
      alert_threshold = 500ms

Absolute timestamps

You can reference specific moments in time using absolute timestamps. This is useful when you know the exact time of an incident or need to analyze data from a specific date.

ISO 8601 format

Create absolute timestamps using the ts() function with ISO 8601 formatted strings. This format works with or without timezone information:

# Absolute timestamps using ISO 8601 format
DEF timestamps =
  SET incident_start = ts('2024-01-15T14:30:00'),
      maintenance_window = ts('2024-01-15T02:00:00Z'),
      deployment_time = ts('2024-01-15T09:15:30.123Z')

Current time

Get the current timestamp using the now() function. This captures the exact moment when your query starts running:

# Get the current timestamp
DEF current_times =
  SET right_now = now(),
      query_start_time = now()

Time truncation with @

The @ operator snaps timestamps to time boundaries. You can truncate any timestamp to the start of its hour, day, week, or other time period. This makes it straightforward to group events into time buckets for analysis.

Truncation syntax

Apply the @ operator to any timestamp to round it down to the nearest time boundary:

# Truncate current time to various boundaries
now()@d         # Today at midnight (00:00:00)
now()@hr        # This hour at :00 minutes
now()@min       # This minute at :00 seconds
now()@w         # This week's Monday at midnight
now()@mon       # First day of this month at midnight

Available truncation units

Unit	Truncates To	Example Result
@s	Start of second	2024-01-15T14:30:25.000
@min	Start of minute	2024-01-15T14:30:00.000
@hr	Start of hour	2024-01-15T14:00:00.000
@d	Start of day (midnight)	2024-01-15T00:00:00.000
@w	Start of week (Monday)	2024-01-15T00:00:00.000
@mon	Start of month	2024-01-01T00:00:00.000
@yr	Start of year	2024-01-01T00:00:00.000

Multi-unit truncation

You can truncate to multiples of a time unit by placing a number between @ and the unit. For sub-day units (seconds, minutes, hours) and days, this uses epoch-modular arithmetic -- buckets are evenly spaced from the Unix epoch and can cross parent-unit boundaries (e.g., @7hr buckets roll across midnight). For calendar units (months, quarters, years), buckets are anchored to the start of the year.

# Truncate to 2-hour boundaries (00:00, 02:00, 04:00, ...)
now()@2hr

# Truncate to 15-minute boundaries (xx:00, xx:15, xx:30, xx:45)
now()@15min

# Truncate to 30-second boundaries
now()@30s

# Truncate to 4-hour boundaries (00:00, 04:00, 08:00, ...)
now()@4hr

# Truncate to 3-month (quarterly) boundaries
now()@3mon

For example, if the current time is 2024-01-15T14:37:22:

Expression	Result
@2hr	2024-01-15T14:00:00
@4hr	2024-01-15T12:00:00
@15min	2024-01-15T14:30:00
@30s	2024-01-15T14:37:00

Multi-unit truncation works with all supported time units. A plain @hr is equivalent to @1hr.

When the multiplier doesn't evenly divide the parent unit's range (e.g., @7hr doesn't evenly divide 24 hours), the buckets roll continuously across boundaries rather than resetting. For example, @7hr produces buckets like ...21:00, 04:00, 11:00, 18:00, 01:00... rather than resetting at midnight. Similarly, @3d produces uniform 3-day buckets from the epoch that cross month boundaries freely.

Truncation with any timestamp

You can truncate any timestamp, not just now(). Create time buckets from your event data by truncating timestamp fields:

# Truncate any timestamp, not just now()
DEF event_data =
  SET event_time = ts('2024-01-15T14:37:22');

FROM event_data
| SET hour_bucket = event_time@hr        // 2024-01-15T14:00:00
| SET day_bucket = event_time@d          // 2024-01-15T00:00:00

You can also truncate timestamp fields directly from your event datasets to group events by time periods:

FROM events
| SET event_day = timestamp@d           // Group events by day
| SET event_hour = timestamp@hr         // Group events by hour
| SET event_4hr = timestamp@4hr         // Group events by 4-hour blocks
| SELECT user_id, event_day, event_hour, event_4hr, action

Time ranges

You combine time values into ranges using the .. operator. Time ranges let you express time spans like "between 2 and 4 hours ago" or "from this morning onward." This makes it natural to filter events within specific time windows.

The range operator ..

The .. operator creates a span between two time points. You place time values on either side to define the start and end of your range.

Create a range between 2 hours ago and 1 hour ago:

-2hr..-1hr

Create a range from a specific time until now:

ts('2024-01-15T10:00:00')..now()

Create a range from midnight today until midnight tomorrow:

now()@d..(now()@d + 1d)

Relative time ranges

Use negative numbers to go back in time from "now". This pattern covers most security and operational analytics scenarios where you're investigating recent events.

Get events from the last hour:

-1hr..now()

Get events between 2 and 4 hours ago:

-4hr..-2hr

Get events from this week so far:

now()@w..now()

You can combine truncation with ranges to create precise time windows aligned to calendar boundaries:

// From start of today until now
now()@d..now()

// Yesterday (full day)
(now()@d - 1d)..(now()@d)

// Last full hour
(now()@hr - 1hr)..(now()@hr)

Unbounded ranges

You can leave either side of the range empty to create spans that extend infinitely in one direction. This is useful for ongoing monitoring or historical analysis without a specific end point.

Get everything before 1 hour ago:

..-1hr

Get everything from a specific time onward:

ts('2024-01-15T10:00:00')..

Get everything from 2 hours ago onward (includes future events):

-2hr..

Bounded vs unbounded ranges

The choice between bounded and unbounded ranges determines how your queries behave, especially for ongoing monitoring versus historical analysis.

// Bounded: Only gets events that happened in the past hour
FROM alerts | WHERE timestamp IN -1hr..now()

// Unbounded: Gets past events AND future events as they arrive
FROM alerts | WHERE timestamp IN -1hr..

Use bounded ranges when analyzing completed time periods. Use unbounded ranges when monitoring ongoing events as they happen.