Skip to main content
A common need in offline-first apps is syncing data based on time, for example, only syncing issues updated in the last 7 days instead of the entire dataset. You might expect to write something like: 
# Sync Streams
streams:
  issues_after_start_date:
  query: SELECT * FROM issues WHERE updated_at > subscription.parameter('start_at')

# Sync Rules
bucket_definitions:
  issues_after_start_date:
    parameters: SELECT request.parameters() ->> 'start_at' as start_at
    data: SELECT * FROM issues WHERE updated_at > bucket.start_date
However, this won’t work. Here’s why.

​
The Problem

PowerSync pre-computes and caches which rows belong to which parameters to enable efficient streaming. This means parameter-based filtering is limited to equality checks (=, IN, IS NULL) — range operators like >, <, >=, or <= are not supported on parameters. Additionally, time-based functions like now() aren’t allowed in parameter expressions because the result changes depending on when the query runs, making pre-computation impossible. These constraints apply to both Sync Streams and legacy Sync Rules. This guide covers a few practical workarounds.
We are working on a more elegant solution for this problem. When ready, this guide will be updated accordingly.

​
Workarounds

​
1: Pre-Defined Time Ranges

Add a boolean column to your table that indicates whether a row falls within a specific time range. Keep this column updated in your source database using a scheduled job. For example, add an updated_this_week column: 
ALTER TABLE issues ADD COLUMN updated_this_week BOOLEAN DEFAULT false;
Update it periodically using a cron job (e.g., with pg_cron): 
UPDATE issues SET updated_this_week = (updated_at > now() - interval '7 days');

config:
  edition: 3
streams:
  recent_issues:
    auto_subscribe: true
    query: SELECT * FROM issues WHERE updated_this_week = true
For multiple time ranges, define a stream per range and let the client subscribe to the one it needs:
config:
  edition: 3
streams:
  issues_1week:
    query: SELECT * FROM issues WHERE updated_this_week = true

  issues_1month:
    query: SELECT * FROM issues WHERE updated_this_month = true
The client subscribes to the desired range:
// Subscribe to one-week range
await db.syncStream('issues_1week').subscribe();
// Or subscribe to one-month range
await db.syncStream('issues_1month').subscribe();
This approach works well when you have a small, fixed set of time ranges. However, it requires schema changes and a scheduled job to keep the columns updated.
This approach requires schema changes and scheduled jobs (e.g., pg_cron). Limited to pre-defined time ranges.
If you need more flexibility like letting users pick arbitrary date ranges, see Workaround 2 below.

​
2: Buckets Per Date

Instead of pre-defined ranges, create a bucket for each date and let the client specify which dates to sync. Use substring to extract the date portion from a timestamp and match it with =: For a complete working example, see the PowerSync + Supabase: Time-Based Sync demo. 
config:
  edition: 3
streams:
  issues_by_date:
    query: SELECT * FROM issues WHERE substring(updated_at, 1, 10) = subscription.parameter('date')
The client subscribes once per date it wants to sync:
await db.syncStream('issues_by_date', { date: '2026-01-07' }).subscribe();
await db.syncStream('issues_by_date', { date: '2026-01-08' }).subscribe();
await db.syncStream('issues_by_date', { date: '2026-01-09' }).subscribe();
Each subscription can be managed independently — you can subscribe and unsubscribe to individual dates without affecting others.
This gives users full control over which dates to sync, with no schema changes or scheduled jobs required. The trade-off is granularity. In this example we’re using daily buckets. If you need finer precision (hourly), syncing a large range means many buckets, which can degrade sync performance and approach PowerSync’s limit of 1,000 buckets per user. If you use larger buckets (monthly), you lose the ability to filter accurately.
You must commit to a single granularity. Daily = too many buckets for long ranges. Monthly = lose precision for recent data.
You have to pick a granularity and stick with it. If that’s a problem—say, you want hourly precision for recent data but don’t want hundreds of buckets when syncing a full month, see Workaround 3 below.

​
3: Multiple Granularities

Combine multiple granularities in a single definition. This lets you use larger buckets (days) for older data and smaller buckets (hours, minutes) for recent data. 
config:
  edition: 3
streams:
  issues_by_partition:
    queries:
      # By day (e.g., "2026-01-07")
      - SELECT * FROM issues WHERE substring(updated_at, 1, 10) = subscription.parameter('partition')
      # By hour (e.g., "2026-01-07T14")
      - SELECT * FROM issues WHERE substring(updated_at, 1, 13) = subscription.parameter('partition')
      # By 10 minutes (e.g., "2026-01-07T14:3")
      - SELECT * FROM issues WHERE substring(updated_at, 1, 15) = subscription.parameter('partition')
The client subscribes once per partition, mixing granularities as needed:
await db.syncStream('issues_by_partition', { partition: '2026-01-05' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-06' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-07T10' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-07T11' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-07T12:0' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-07T12:1' }).subscribe();
await db.syncStream('issues_by_partition', { partition: '2026-01-07T12:2' }).subscribe();
Each query naturally acts as a filter based on the length of the partition value — a day-format partition only matches the day query, an hour-format partition only matches the hour query, and so on.
This syncs January 5–6 by day, the morning of January 7 by hour, and the last 30 minutes in 10-minute chunks, without creating hundreds of buckets. The trade-off is complexity. The client must decide which granularity to use for each time segment, and each row belongs to multiple buckets, which increases replication overhead.
When using multiple time granularities (e.g., monthly, daily, hourly), rows move between buckets as time passes. Since each granularity creates a different bucket ID, the client must re-download the row from the new bucket even if it already has the data. This re-download overhead can nullify the benefits of granular filtering. For this reason, in some cases it may be better to sync entire months avoiding the re-sync overhead, even if you sync more data initially.
Each row belongs to multiple buckets (replication overhead). Re-sync overhead when rows move between bucket granularities. Added complexity may not justify the gains over Workaround 2.

​
Conclusion

Time-based sync is a common need, but PowerSync doesn’t support range operators or time-based functions on parameters directly. To recap the workarounds:
  • Pre-defined time ranges — Simplest option. Use when you have a fixed set of time ranges and don’t mind schema changes.
  • Buckets Per Date — More flexible. Use when you need arbitrary date ranges but can live with a single granularity.
  • Multiple Granularities — Most flexible. Use when you need precision for recent data without syncing hundreds of buckets. Be mindful of the re-sync overhead.
We’re working on a more elegant solution. This guide will be updated when it’s ready.