feat(usage): add flexible time windows and include current month in usage analytics - #1501
Conversation
… boundary alignment
|
This PR was auto-closed. Only contributors approved with Maintainers review auto-closed issues daily. Issues that do not meet the quality bar in CONTRIBUTING.md will not be reopened or receive a reply. If a maintainer replies See CONTRIBUTING.md. |
|
@cubic-dev-ai review this PR |
@jaiswalabhishek377 I have started the AI code review. It will take a few minutes to complete. |
![cubic-dev-ai[bot]](https://avatars.githubusercontent.com/in/1082092?s=60&v=4){kind=small}
There was a problem hiding this comment.
5 issues found across 7 files
Reply with feedback, questions, or to request a fix.
Re-trigger cubic
Prompt for AI agents (unresolved issues)
Check if these issues are valid — if so, understand the root cause of each and fix them. If appropriate, use sub-agents to investigate and fix each issue separately.
<file name="apps/backend/src/utils/date.ts">
<violation number="1" location="apps/backend/src/utils/date.ts:39">
P2: When a usage query crosses a UTC boundary, separate `new Date()` snapshots can make its SQL window and zero-filled series use different periods. Resolve the window once and pass that snapshot or resolved result through the query and date-series generation.</violation>
<violation number="2" location="apps/backend/src/utils/date.ts:60">
P2: When a period is overridden to a finer bucket, `Math.round` adds a bucket before the requested lookback after the current bucket is sufficiently far underway. For example, `6m` with daily granularity can render December 31 even though the query starts January 1. Compute the count from UTC bucket boundaries instead of rounded elapsed milliseconds.</violation>
<violation number="3" location="apps/backend/src/utils/date.ts:60">
P2: When a non-default granularity is selected, rounding elapsed time counts a partial current bucket as a full extra bucket. This makes `6m`/`day` render a date before its SQL lookback, and day-period/hour renders an extra hour after `:30`; derive counts from UTC bucket boundaries instead.</violation>
</file>
<file name="apps/frontend/src/routes/_sidebar-layout.settings.usage.tsx">
<violation number="1" location="apps/frontend/src/routes/_sidebar-layout.settings.usage.tsx:104">
P2: For `day` granularity, `value` is a UTC `YYYY-MM-DD` bucket, but `new Date(value)` parses it at UTC midnight. Users west of UTC therefore see every daily label one day early; parse date components as local values before formatting.</violation>
</file>
<file name="apps/backend/src/queries/usage.queries.ts">
<violation number="1" location="apps/backend/src/queries/usage.queries.ts:259">
P2: When `period: '6m'` is requested with `granularity: 'day'` after 12:00 UTC, this returns an extra leading zero day. Compute the series length from UTC calendar dates or pass the resolved start/count through instead of letting `fillMissingDates` recompute it with rounded elapsed time.</violation>
</file>
| count: number; | ||
| startDate: Date; | ||
| } { | ||
| const now = new Date(); |
There was a problem hiding this comment.
P2: When a usage query crosses a UTC boundary, separate new Date() snapshots can make its SQL window and zero-filled series use different periods. Resolve the window once and pass that snapshot or resolved result through the query and date-series generation.
Prompt for AI agents
Check if this issue is valid — if so, understand the root cause and fix it. At apps/backend/src/utils/date.ts, line 39:
<comment>When a usage query crosses a UTC boundary, separate `new Date()` snapshots can make its SQL window and zero-filled series use different periods. Resolve the window once and pass that snapshot or resolved result through the query and date-series generation.</comment>
<file context>
@@ -9,24 +12,67 @@ export function isValidIsoDateString(s: string): boolean {
+ count: number;
+ startDate: Date;
+} {
+ const now = new Date();
+ const period =
+ options?.period && PERIOD_CONFIG[options.period]
</file context>
| (now.getUTCMonth() - startDate.getUTCMonth()) + | ||
| 1; | ||
| } else if (granularity === 'day') { | ||
| count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)) + 1); |
There was a problem hiding this comment.
P2: When a period is overridden to a finer bucket, Math.round adds a bucket before the requested lookback after the current bucket is sufficiently far underway. For example, 6m with daily granularity can render December 31 even though the query starts January 1. Compute the count from UTC bucket boundaries instead of rounded elapsed milliseconds.
Prompt for AI agents
Check if this issue is valid — if so, understand the root cause and fix it. At apps/backend/src/utils/date.ts, line 60:
<comment>When a period is overridden to a finer bucket, `Math.round` adds a bucket before the requested lookback after the current bucket is sufficiently far underway. For example, `6m` with daily granularity can render December 31 even though the query starts January 1. Compute the count from UTC bucket boundaries instead of rounded elapsed milliseconds.</comment>
<file context>
@@ -9,24 +12,67 @@ export function isValidIsoDateString(s: string): boolean {
+ (now.getUTCMonth() - startDate.getUTCMonth()) +
+ 1;
+ } else if (granularity === 'day') {
+ count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)) + 1);
+ } else {
+ count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (60 * 60 * 1000)) + 1);
</file context>
| const [y, m] = value.split('-').map(Number); | ||
| return format(new Date(y, m - 1, 1), dateFormats.month); | ||
| } | ||
| return format(new Date(value), dateFormats[granularity]); |
There was a problem hiding this comment.
P2: For day granularity, value is a UTC YYYY-MM-DD bucket, but new Date(value) parses it at UTC midnight. Users west of UTC therefore see every daily label one day early; parse date components as local values before formatting.
Prompt for AI agents
Check if this issue is valid — if so, understand the root cause and fix it. At apps/frontend/src/routes/_sidebar-layout.settings.usage.tsx, line 104:
<comment>For `day` granularity, `value` is a UTC `YYYY-MM-DD` bucket, but `new Date(value)` parses it at UTC midnight. Users west of UTC therefore see every daily label one day early; parse date components as local values before formatting.</comment>
<file context>
@@ -95,6 +96,14 @@ function UsagePage() {
+ const [y, m] = value.split('-').map(Number);
+ return format(new Date(y, m - 1, 1), dateFormats.month);
+ }
+ return format(new Date(value), dateFormats[granularity]);
+}
+
</file context>
| return format(new Date(value), dateFormats[granularity]); | |
| if (granularity === 'day' && /^\d{4}-\d{2}-\d{2}$/.test(value)) { | |
| const [y, m, d] = value.split('-').map(Number); | |
| return format(new Date(y, m - 1, d), dateFormats.day); | |
| } | |
| return format(new Date(value), dateFormats[granularity]); |
| .groupBy(({ date }) => date); | ||
|
|
||
| return fillMissingDates(rows.map(normalizeMessageUsageRow), granularity); | ||
| return fillMissingDates(rows.map(normalizeMessageUsageRow), granularity, period); |
There was a problem hiding this comment.
P2: When period: '6m' is requested with granularity: 'day' after 12:00 UTC, this returns an extra leading zero day. Compute the series length from UTC calendar dates or pass the resolved start/count through instead of letting fillMissingDates recompute it with rounded elapsed time.
Prompt for AI agents
Check if this issue is valid — if so, understand the root cause and fix it. At apps/backend/src/queries/usage.queries.ts, line 259:
<comment>When `period: '6m'` is requested with `granularity: 'day'` after 12:00 UTC, this returns an extra leading zero day. Compute the series length from UTC calendar dates or pass the resolved start/count through instead of letting `fillMissingDates` recompute it with rounded elapsed time.</comment>
<file context>
@@ -253,12 +256,16 @@ export const getMessagesUsage = async (projectId: string, filter: UsageFilter):
.groupBy(({ date }) => date);
- return fillMissingDates(rows.map(normalizeMessageUsageRow), granularity);
+ return fillMissingDates(rows.map(normalizeMessageUsageRow), granularity, period);
};
</file context>
| (now.getUTCMonth() - startDate.getUTCMonth()) + | ||
| 1; | ||
| } else if (granularity === 'day') { | ||
| count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)) + 1); |
There was a problem hiding this comment.
P2: When a non-default granularity is selected, rounding elapsed time counts a partial current bucket as a full extra bucket. This makes 6m/day render a date before its SQL lookback, and day-period/hour renders an extra hour after :30; derive counts from UTC bucket boundaries instead.
Prompt for AI agents
Check if this issue is valid — if so, understand the root cause and fix it. At apps/backend/src/utils/date.ts, line 60:
<comment>When a non-default granularity is selected, rounding elapsed time counts a partial current bucket as a full extra bucket. This makes `6m`/`day` render a date before its SQL lookback, and day-period/hour renders an extra hour after `:30`; derive counts from UTC bucket boundaries instead.</comment>
<file context>
@@ -9,24 +12,67 @@ export function isValidIsoDateString(s: string): boolean {
+ (now.getUTCMonth() - startDate.getUTCMonth()) +
+ 1;
+ } else if (granularity === 'day') {
+ count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (24 * 60 * 60 * 1000)) + 1);
+ } else {
+ count = Math.max(1, Math.round((now.getTime() - startDate.getTime()) / (60 * 60 * 1000)) + 1);
</file context>
|
Hi @Bl3f I’ve resolved all of them in the latest commits on my branch (commit b304103) with 100% passing tests and lint checks. Branch: main...jaiswalabhishek377:nao:feat/usage-flexible-time-windows |
1 participant
Fixes #1482
Summary of Changes
1. Flexible Time Windows
Last 7 days,Last 30 days,Last 60 days, andLast 90 daysoptions to the usage analytics filter alongside existingLast 24 hours,Last 15 days, andLast 6 months.period(time lookback window) fromgranularity(SQL aggregation bucket size).24h -> hour,7d..90d -> day,6m -> month), while still supporting independent granularity overrides if needed.2. Root Causes & Fix for Missing Current Month
getLookbackTimestamp('month')subtracted an approximate 180 days (now - 6 * 30 * 24 * 60 * 60 * 1000), causing boundary cutoffs.It now calculates the 1st day of the month 5 months ago at
00:00:00 UTC, covering the 5 preceding complete months plus the active current month (6 months total).format(new Date('YYYY-MM'), 'MMM yyyy')was parsing month strings as UTC midnight (2026-08-01T00:00:00Z). In timezones with negative UTC offsets (e.g. US/Canada UTC-4 to UTC-8), this timestamp resolved to July 31st local time, shifting all 6 month labels backward by 1 month and dropping August.fix-Added local date component parsing (
formatChartXAxisLabel) to preserve the exact month string.3. Backward Compatibility & Test Coverage
?granularity=...are seamlessly parsed and mapped to the correspondingperiod.@nao/backendverifying30d,60d,90d, and6mlookback query outputs.Key Files Modified
apps/backend/src/types/usage.ts: AddedusagePeriodSchema('24h' | '7d' | '15d' | '30d' | '60d' | '90d' | '6m') and updatedusageFilterSchema.apps/backend/src/utils/date.ts: AddedPERIOD_CONFIG,resolvePeriodAndGranularity, updatedgetLookbackTimestamp,generateDateSeries, andfillMissingDates.apps/backend/src/queries/usage.queries.ts: ResolvedperiodandgranularityingetMessagesUsageandgetTotalUsage.apps/backend/tests/usage-queries.test.ts: Added unit tests for 30d, 60d, 90d, and 6m lookbacks.apps/frontend/src/components/settings/usage-route-search.ts: Addedperiodto search params, storage, and fallback validators.apps/frontend/src/components/settings/usage-filters.tsx: Added new period options to dropdown.apps/frontend/src/routes/_sidebar-layout.settings.usage.tsx: Passedperiodto queries and added timezone-safeformatChartXAxisLabel.Testing & Verification
npm run test -w @nao/backend -- tests/usage-queries.test.ts— all 4 test suites passed.npm run lintacross@nao/backend,@nao/frontend, and@nao/sharedwith 0 errors.Last 24 hours,Last 7 days,Last 15 days,Last 30 days,Last 60 days,Last 90 days, andLast 6 months) onlocalhost:3000.Last 6 monthsaccurately renders the active current month (August) across different timezones without offset shifts.Screenshots / Proof of Fix
Before:
After:
Tested Locally on the running frontend and backend.