Feature: Support now() / Dynamic Date Values in Segment Filters for Date/Time Fields

[thienscmon]

Description

Currently, segment filters with date value types (field type time) require static datetime values when using operators like before_date, after_date, in_date_range, and not_in_date_range. While in_the_last_days provides relative date filtering for "past N days", there is no way to filter based on today's date or other relative time expressions like now(), today(), start_of_day(), end_of_day(), etc.

---

Use Cases

Use Case 1: Users Who Signed Up Today

A common use case is creating a segment for users who signed up today:

created_at >= start_of_today AND created_at < end_of_today

Currently, users must:

1. Manually set the date filter to today's specific date (e.g., 2025-12-01)
2. Update the segment daily to keep it accurate
3. Or use in_the_last_days: 1 which includes the last 24 hours (not exactly "today")

This is cumbersome and error-prone for dynamic segmentation needs.

Use Case 2: Sequential Onboarding Email Series

For onboarding email campaigns, you want to send a series of emails on consecutive days after signup. For example, a 6-day onboarding sequence:

Email	Target Segment
Email 1 (Day 0)	Users whose signup date is today (00:00 - 23:59)
Email 2 (Day 1)	Users whose signup date was yesterday (exactly 1 day ago)
Email 3 (Day 2)	Users whose signup date was 2 days ago
Email 4 (Day 3)	Users whose signup date was 3 days ago
Email 5 (Day 4)	Users whose signup date was 4 days ago
Email 6 (Day 5)	Users whose signup date was 5 days ago

The key requirement is filtering by exact calendar day relative to today, not a rolling 24-hour window. For example:

• "Day 3" segment should include users who signed up exactly 3 calendar days ago (from 00:00:00 to 23:59:59 of that day in the workspace timezone)
• This ensures each user receives exactly one email per day in the sequence, without overlap or gaps

Current workaround is impossible because:

• in_the_last_days: 3 includes days 0, 1, 2, and 3 (not just day 3)
• Static date ranges require daily manual updates
• No way to express "exactly N days ago"

---

Proposed Solution

Option 1: Add New Relative Date Operators

Add new operators specifically for common relative date scenarios:

Operator	Description	SQL Equivalent
is_today	Filter for records on current date (Day 0)	DATE(field AT TIME ZONE tz) = CURRENT_DATE
is_yesterday	Filter for records on previous date (Day 1)	DATE(field AT TIME ZONE tz) = CURRENT_DATE - 1
is_n_days_ago	Filter for records exactly N days ago	DATE(field AT TIME ZONE tz) = CURRENT_DATE - N
is_this_week	Filter for records in current week	field >= date_trunc('week', NOW())
is_this_month	Filter for records in current month	field >= date_trunc('month', NOW())

Option 2: Support Dynamic Value Placeholders

Allow special placeholder values in string_values that are evaluated at query time:

{
  "field_name": "created_at",
  "field_type": "time",
  "operator": "in_date_range",
  "string_values": ["$START_OF_TODAY", "$END_OF_TODAY"]
}

Supported placeholders:

• $NOW → NOW()
• $START_OF_TODAY → date_trunc('day', NOW() AT TIME ZONE tz)
• $END_OF_TODAY → date_trunc('day', NOW() AT TIME ZONE tz) + INTERVAL '1 day' - INTERVAL '1 second'
• $START_OF_N_DAYS_AGO(3) → date_trunc('day', NOW() AT TIME ZONE tz) - INTERVAL '3 days'
• $END_OF_N_DAYS_AGO(3) → date_trunc('day', NOW() AT TIME ZONE tz) - INTERVAL '2 days' - INTERVAL '1 second'

Option 3: Add exactly_n_days_ago Operator (Recommended for Use Case 2)

Add a new operator specifically for "exactly N calendar days ago":

{
  "field_name": "created_at",
  "field_type": "time",
  "operator": "exactly_n_days_ago",
  "string_values": ["3"]  // Day 3 of onboarding
}

Generated SQL (respecting timezone):

DATE(created_at AT TIME ZONE 'Asia/Ho_Chi_Minh') = CURRENT_DATE - INTERVAL '3 days'

This would enable creating 6 segments for a 6-day onboarding sequence:

• Segment "Day 0": exactly_n_days_ago: 0 (today)
• Segment "Day 1": exactly_n_days_ago: 1 (yesterday)
• Segment "Day 2": exactly_n_days_ago: 2
• ... and so on

---

[pierre-b]

Hi @thienscmon , if Notifuse provided an automation feature, with triggers and workflow nodes to orchestrate welcome series, would you still need this segment operators?

[pierre-b]

Hi @thienscmon , since v20 Notifuse has an "Automation" feature, let me know if it covers your need and what remains to implement on the segment side please!