Add `per_second` function support for `timechart` command by dai-chen · Pull Request #4464 · opensearch-project/sql

dai-chen · 2025-10-08T22:46:50Z

Description

This PR adds per_second() support to the timechart command by implementing Option 3 (Eval Transformation). As a short-term solution, it extends the Timechart AST to recognize the per_second function and rewrites into the equivalent math formula shown in the examples below. See issue #4350 for background and alternatives.

Examples

-- Original
source=events
| timechart span=5m per_second(packets)

-- Rewritten
source=logs
| timechart span=5m sum(packets) as `per_second(packets)`
| eval `per_second(packets)` = `per_second(packets)` / 300   -- 5 minutes = 300 seconds

For spans whose length varies with the calendar (month/quarter/year), the number of seconds depends on actual start/end timestamps (e.g., September has 30 days; October has 31 days; February in a leap year has 29 days). To ensure correctness, the rewrite computes the exact bucket length dynamically:

-- Example: span=2mon
source=logs
| timechart span=2mon sum(packets) as "per_second(packets)"
| eval `per_second(packets)` = `per_second(packets)` /
    timestampdiff(
      SECOND,
      @timestamp,                                  -- span start
      timestampadd(MONTH, 2, @timestamp)           -- span end   (start + 2 months)
    )

Related Issues

Resolves partially #4350.

Check List

New functionality includes testing.
New functionality has been documented.
New functionality has javadoc added.
New functionality has a user manual doc added.
New PPL command checklist all confirmed.
API changes companion pull request created.
Commits are signed per the DCO using --signoff or -s.
Public documentation issue/PR created.

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Signed-off-by: Chen Dai <daichen@amazon.com>

RyanL1997

Hi @dai-chen , thanks for the change.

RyanL1997 · 2025-10-10T21:14:59Z

core/src/main/java/org/opensearch/sql/ast/tree/Timechart.java

+
+    return eval(
+        timechart(AstDSL.alias(perFunc.aggName, sum(perFunc.aggArg))),
+        let(perFunc.aggName).multiply(perFunc.seconds).dividedBy(spanSeconds));


What happens if the span evaluates to 0 seconds (e.g., with millisecond spans or edge cases)? Should there be validation or error handling for division by zero?

Good catch. Let me check if timechart has the validation or not. Thanks!

I quick tested timechart command and found negative span value is not allowed in our grammar. But zero-span validation is missing. Let me create an issue and raise separate PR to fix.

opensearchsql> source=test_data_2023 | timechart span=0m per_second(packets); TransportError(500, 'SearchPhaseExecutionException', {'error': {'reason':'Error occurred in OpenSearch engine: all shards failed', 'details': 'Shard[0]: java.lang.IllegalArgumentException: Zero or negative time interval not supported\n\n For more details, please send request for Json format to see the raw response from OpenSearch engine.', 'type': 'SearchPhaseExecutionException'}, 'status': 400}) opensearchsql> source=test_data_2023 | timechart span=-1m per_second(packets); {'reason': 'Invalid Query', 'details': "[-] is not a valid term at this part of the query: '...23 | timechart span=-' <-- HERE. extraneous input '-' expecting {SPANLENGTH, INTEGER_LITERAL}", 'type': 'SyntaxCheckException'}

Created issue: #4527. Thanks!

core/src/main/java/org/opensearch/sql/ast/tree/Timechart.java

core/src/main/java/org/opensearch/sql/calcite/utils/PlanUtils.java

integ-test/src/test/java/org/opensearch/sql/calcite/remote/CalciteTimechartPerFunctionIT.java

Signed-off-by: Chen Dai <daichen@amazon.com>

Add per_second() support to the timechart command by implementing Option 3 (Eval Transformation). --------- Signed-off-by: Chen Dai <daichen@amazon.com> (cherry picked from commit 4d416db) Signed-off-by: github-actions[bot] <github-actions[bot]@users.noreply.github.com>

) Add per_second() support to the timechart command by implementing Option 3 (Eval Transformation). --------- (cherry picked from commit 4d416db) Signed-off-by: Chen Dai <daichen@amazon.com> Signed-off-by: github-actions[bot] <github-actions[bot]@users.noreply.github.com> Signed-off-by: Lantao Jin <ltjin@amazon.com> Co-authored-by: github-actions[bot] <github-actions[bot]@users.noreply.github.com> Co-authored-by: Lantao Jin <ltjin@amazon.com>

dai-chen added 22 commits September 30, 2025 09:40

Add grammar and AST for per_second function with UT and IT

29d4bf8

Signed-off-by: Chen Dai <daichen@amazon.com>

Translate per_second function call to eval + timechart

9aff342

Signed-off-by: Chen Dai <daichen@amazon.com>

Add SparkSQL verify test

86920fa

Signed-off-by: Chen Dai <daichen@amazon.com>

Fix aggregate function alias issue for timechart visit

1276017

Signed-off-by: Chen Dai <daichen@amazon.com>

Hide internal sum aggregation in output

0ae12ff

Signed-off-by: Chen Dai <daichen@amazon.com>

Leverage AST DSL to make rewrite more readable

3caab3c

Signed-off-by: Chen Dai <daichen@amazon.com>

Add UT for transform per function in Timechart AST

875d883

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor UT with parameterized test

346cc5e

Signed-off-by: Chen Dai <daichen@amazon.com>

Avoid function name hardcoding in visitPerFunctionCall

ac88a6f

Signed-off-by: Chen Dai <daichen@amazon.com>

Merge branch 'main' into add-per-second-function-support-eval-post-agg

69d07a8

Signed-off-by: Chen Dai <daichen@amazon.com>

Calculate interval seconds dynamically by timestampdiff

ab05e3d

Signed-off-by: Chen Dai <daichen@amazon.com>

Fix broken IT

cf32829

Signed-off-by: Chen Dai <daichen@amazon.com>

Fix broken UT and integer division issue

4e09326

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor transform method with fluent AST DSL APIs

768ba11

Signed-off-by: Chen Dai <daichen@amazon.com>

Extract span unit to interval unit util

5081528

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor transform method with built-in DSL APIs

e1cb01c

Signed-off-by: Chen Dai <daichen@amazon.com>

Fix broken AST builder UT

5a4400c

Signed-off-by: Chen Dai <daichen@amazon.com>

Update javadoc and remove old ITs

47cfd71

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor per function rate expresion build

79efdb3

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor test data into json files

38a1039

Signed-off-by: Chen Dai <daichen@amazon.com>

Add explain IT

832528f

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor IT test data and cases

f2fedd3

Signed-off-by: Chen Dai <daichen@amazon.com>

dai-chen self-assigned this Oct 8, 2025

dai-chen added enhancement New feature or request PPL Piped processing language backport 2.19-dev labels Oct 8, 2025

dai-chen added 4 commits October 9, 2025 09:25

Rename IT test dataset

5ecf154

Signed-off-by: Chen Dai <daichen@amazon.com>

Refactor per second related logic

aef965a

Signed-off-by: Chen Dai <daichen@amazon.com>

Update doctest

83ee450

Signed-off-by: Chen Dai <daichen@amazon.com>

Rename transform function

519ad59

Signed-off-by: Chen Dai <daichen@amazon.com>

dai-chen requested review from GumpacG, LantaoJin, RyanL1997, Yury-Fridlyand, acarbonetto, anirudha, forestmvey, noCharger, qianheng-aws, ykmr1224 and yuancu as code owners October 9, 2025 21:57

RyanL1997 reviewed Oct 10, 2025

View reviewed changes

ykmr1224 previously approved these changes Oct 10, 2025

View reviewed changes

core/src/main/java/org/opensearch/sql/calcite/utils/PlanUtils.java Outdated Show resolved Hide resolved

ahkcs reviewed Oct 10, 2025

View reviewed changes

integ-test/src/test/java/org/opensearch/sql/calcite/remote/CalciteTimechartPerFunctionIT.java Show resolved Hide resolved

Address PR comments

995171e

Signed-off-by: Chen Dai <daichen@amazon.com>

dai-chen dismissed ykmr1224’s stale review via 995171e October 13, 2025 17:48

dai-chen requested review from ahkcs and ykmr1224 October 13, 2025 17:51

RyanL1997 approved these changes Oct 13, 2025

View reviewed changes

ahkcs approved these changes Oct 13, 2025

View reviewed changes

dai-chen mentioned this pull request Oct 13, 2025

[Bug] timechart command with zero span throws execution exception #4527

Closed

Merge branch 'main' into add-per-second-function-support-eval-post-agg

c2d96c1

ykmr1224 approved these changes Oct 13, 2025

View reviewed changes

dai-chen merged commit 4d416db into opensearch-project:main Oct 13, 2025
33 checks passed

opensearch-trigger-bot bot mentioned this pull request Oct 13, 2025

[Backport 2.19-dev] Add per_second function support for timechart command #4530

Merged

dai-chen mentioned this pull request Oct 13, 2025

Add per_minute, per_hour, per_day function support #4531

Merged

8 tasks

This was referenced Jan 11, 2026

docs: add PPL Timechart Functions report for v3.4.0 tkykenmt/opensearch-feature-explorer#1856

Merged

[feature] PPL Timechart Functions tkykenmt/opensearch-feature-explorer#1624

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add `per_second` function support for `timechart` command#4464

Add `per_second` function support for `timechart` command#4464
dai-chen merged 28 commits intoopensearch-project:mainfrom
dai-chen:add-per-second-function-support-eval-post-agg

dai-chen commented Oct 8, 2025 •

edited

Loading

Uh oh!

RyanL1997 left a comment

Uh oh!

RyanL1997 Oct 10, 2025

Uh oh!

dai-chen Oct 13, 2025 •

edited

Loading

Uh oh!

dai-chen Oct 13, 2025

Uh oh!

dai-chen Oct 13, 2025

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

dai-chen commented Oct 8, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Description

Examples

Related Issues

Check List

Uh oh!

RyanL1997 left a comment

Choose a reason for hiding this comment

Uh oh!

RyanL1997 Oct 10, 2025

Choose a reason for hiding this comment

Uh oh!

dai-chen Oct 13, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

dai-chen Oct 13, 2025

Choose a reason for hiding this comment

Uh oh!

dai-chen Oct 13, 2025

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

dai-chen commented Oct 8, 2025 •

edited

Loading

dai-chen Oct 13, 2025 •

edited

Loading