> ## Documentation Index
> Fetch the complete documentation index at: https://powersync-docs-aurora-postgres-setup.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Sync Data by Time with Sync Streams
> Filter and sync data based on time ranges using Sync Streams, with patterns for recent-only and sliding-window queries.
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:
```yaml theme={null}
streams:
issues_after_start_date:
query: SELECT * FROM issues WHERE updated_at > subscription.parameter('start_at')
```
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.
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:
```sql theme={null}
ALTER TABLE issues ADD COLUMN updated_this_week BOOLEAN DEFAULT false;
```
Update it periodically using a cron job (e.g., with `pg_cron`):
```sql theme={null}
UPDATE issues SET updated_this_week = (updated_at > now() - interval '7 days');
```
Then filter on the column in a stream query:
```yaml theme={null}
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:
```yaml theme={null}
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:
```javascript theme={null}
// 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 `=`:
```yaml theme={null}
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:
```javascript theme={null}
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. For a complete working example, see the [PowerSync + Supabase: Time-Based Sync demo](https://github.com/powersync-ja/powersync-js/tree/main/demos/react-supabase-time-based-sync).
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](/resources/performance-and-limits#limits). 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.
```yaml theme={null}
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:
```javascript theme={null}
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.