influxql-to-greptimedb-sql

name	influxql-to-greptimedb-sql
description	Convert InfluxQL SELECT queries into SQL supported by GreptimeDB and identify semantics that cannot be translated exactly. Use for InfluxDB-to-GreptimeDB migrations, Grafana query rewrites, InfluxQL time-window and fill conversions, function migrations, or requests mentioning "InfluxQL to SQL", "convert InfluxQL", "migrate InfluxDB queries", or "rewrite InfluxQL".

Convert InfluxQL to GreptimeDB SQL

Rewrite InfluxQL queries as reviewable GreptimeDB SQL. Convert queries without executing them unless the user explicitly requests execution.

Workflow

1. Use the inline Reference section for all syntax and semantic mappings.
2. Determine from the request or table schema:
   • the GreptimeDB table corresponding to the measurement;
   • the GreptimeDB TIME INDEX column corresponding to InfluxQL time;
   • the columns corresponding to tags and fields;
   • the GreptimeDB version or current repository documentation, especially when using Range Query.
3. Make minimal, explicit assumptions when missing information does not prevent the main conversion. Ask only when the measurement, time column, or target table mapping changes SQL correctness.
4. Rewrite the query structure before rewriting functions:
   • FROM measurement → FROM table;
   • time predicates → the GreptimeDB time column and INTERVAL;
   • GROUP BY time() → GreptimeDB Range Query with FILL NULL when fill() is omitted, or the matching Range Query fill mode when it is present, and report its window-boundary difference;
   • use date_bin() only for fill(none) or when the user accepts omitted empty windows;
   • tag grouping → GROUP BY or Range Query BY (...);
   • functions → equivalent aggregates, scalar functions, or window expressions.
5. Check the output:
   • order first_value and last_value explicitly by the time column;
   • convert InfluxQL percentile arguments from percentages to SQL values in 0..1;
   • remove /.../ delimiters from regex literals and escape SQL strings correctly;
   • report leading and trailing window differences for every Range Query FILL conversion;
   • never silently drop SLIMIT, SOFFSET, tz(), or unsupported functions;
   • never describe an approximate conversion as exact.

Output

Return a SQL code block first. Add only when needed:

• Assumptions: table, time column, type, or version assumptions;
• Semantic differences: InfluxQL behavior that the SQL does not preserve exactly;
• Validation: compare representative row counts, window boundaries, empty windows, and first or last values.

If a safe conversion is impossible, return the convertible SQL skeleton with TODO at the exact unresolved location. Never invent a GreptimeDB function.

Validation

When the user provides a GreptimeDB connection and explicitly requests validation, run EXPLAIN before a read-only SELECT. Compare at least:

• time-range boundaries;
• GROUP BY time() boundaries and offsets;
• results for each tag group;
• empty windows affected by fill();
• ordering semantics for FIRST, LAST, TOP, and BOTTOM.

Reference

Query structure

InfluxQL	GreptimeDB SQL	Notes
measurement	table	Database, retention policy, and schema/table may not map one-to-one
field key	column	Confirm the type from the table schema
tag key	column	Use as a regular SQL filter or grouping column
WHERE	WHERE	Replace time with the actual TIME INDEX column
ORDER BY time	ORDER BY <time_index>	Preserve ASC or DESC
LIMIT / OFFSET	LIMIT / OFFSET	InfluxQL applies these per series; SQL applies them to the full result by default

Do not guess the target of "database"."retention_policy"."measurement". Require a mapping from the user or confirm it from the target schema.

Time and windows

Durations

InfluxQL	SQL interval
10ns	'10 nanoseconds'::INTERVAL
10u / 10µ	'10 microseconds'::INTERVAL
10ms	'10 milliseconds'::INTERVAL
10s	'10 seconds'::INTERVAL
10m	'10 minutes'::INTERVAL
10h	'10 hours'::INTERVAL
10d	'10 days'::INTERVAL
10w	'10 weeks'::INTERVAL

Expand compound durations, for example 1h30m → '1 hour 30 minutes'::INTERVAL.

Time predicates

-- InfluxQL
SELECT "usage" FROM "cpu" WHERE time >= now() - 1h AND time < now()

-- GreptimeDB SQL, assuming ts is the time index
SELECT usage
FROM cpu
WHERE ts >= now() - '1 hour'::INTERVAL
  AND ts < now();

Preserve open and closed bounds. Do not change < to <=.

InfluxQL GROUP BY time() queries without an explicit time predicate stop at now() by default. Add <time_index> <= now() when exact behavior matters.

GROUP BY time()

InfluxQL defaults an omitted fill() to fill(null). Use Range Query with FILL NULL to fill internal empty windows:

SELECT
  ts,
  host,
  avg(usage) RANGE '5m' FILL NULL AS mean_usage
FROM cpu
WHERE ts >= now() - '1 hour'::INTERVAL
ALIGN '5m' BY (host)
ORDER BY ts;

Use date_bin only for fill(none) or when the user explicitly accepts that empty windows are omitted:

SELECT
  date_bin('5 minutes'::INTERVAL, ts) AS time_bucket,
  host,
  avg(usage) AS mean_usage
FROM cpu
WHERE ts >= now() - '1 hour'::INTERVAL
GROUP BY time_bucket, host
ORDER BY time_bucket;

Represent the offset in time(5m, 2m) with an origin:

date_bin(
  '5 minutes'::INTERVAL,
  ts,
  '1970-01-01T00:02:00Z'::TIMESTAMP
)

Fill

Prefer GreptimeDB Range Query for all InfluxQL time grouping except fill(none). Use the same duration for RANGE and ALIGN to reproduce non-overlapping windows. Always specify BY explicitly to avoid grouping by every primary-key column in the target table.

InfluxQL	GreptimeDB Range Query
omitted fill()	FILL NULL
fill(null)	FILL NULL
fill(previous)	FILL PREV
fill(linear)	FILL LINEAR
fill(0)	FILL 0
fill(none)	Omit FILL, or use a date_bin query

Always report that Range Query FILL only emits windows between each series' first and last data-bearing windows. Unlike InfluxQL, it does not emit leading or trailing windows across the full query time range, and different series may therefore cover different window ranges.

SELECT
  ts,
  host,
  avg(usage) RANGE '5m' FILL PREV AS mean_usage
FROM cpu
WHERE ts >= now() - '1 hour'::INTERVAL
ALIGN '5m' BY (host)
ORDER BY ts;

Use BY () when there is no tag grouping. For time(interval, offset), use an RFC3339 string as the alignment point:

ALIGN '5m' TO '1970-01-01T00:02:00Z' BY (host)

Validate offset behavior with boundary data. Do not append a type cast to the TO literal.

Grafana query variables

When migrating a Grafana panel from an InfluxDB data source to the GreptimeDB data source, rewrite the common macros as follows:

InfluxQL panel query	GreptimeDB SQL panel query
WHERE $timeFilter	WHERE $__timeFilter(<time_index>)
GROUP BY time($__interval)	Aggregate with RANGE '$__interval', then use ALIGN '$__interval'

For example:

SELECT
  ts,
  host,
  avg(usage) RANGE '$__interval' FILL NULL AS mean_usage
FROM cpu
WHERE $__timeFilter(ts)
ALIGN '$__interval' BY (host)
ORDER BY ts;

Preserve any additional panel filters and report the Range Query FILL boundary difference described above.

Functions

Direct or simple rewrites

InfluxQL	GreptimeDB SQL
COUNT(x)	count(x)
COUNT(DISTINCT(x))	count(DISTINCT x)
DISTINCT(x)	SELECT DISTINCT x, equivalent only as a value set
MEAN(x)	avg(x)
MEDIAN(x)	median(x)
SPREAD(x)	max(x) - min(x)
STDDEV(x)	stddev(x)
SUM(x)	sum(x)
FIRST(x)	first_value(x ORDER BY <time_index>)
LAST(x)	last_value(x ORDER BY <time_index>)
MAX(x)	max(x)
MIN(x)	min(x)
PERCENTILE(x, 95)	percentile_cont(x, 0.95), with different algorithms
ABS(x)	abs(x)
CEIL(x)	ceil(x)
FLOOR(x)	floor(x)
LN(x)	ln(x)
LOG2(x)	log2(x)
LOG10(x)	log10(x)
POW(x, y)	power(x, y)
ROUND(x)	round(x)

SQL DISTINCT applies to the complete selected row. Include the series tag columns when distinctness must be scoped per series.

Keep standard trigonometric function names when supported, but confirm them against the target GreptimeDB version.

InfluxQL PERCENTILE selects a discrete source field value, while percentile_cont may interpolate. Always report this semantic difference. Without time grouping, FIRST, LAST, MIN, MAX, and PERCENTILE may also carry the selected point's original timestamp or related columns. A regular SQL aggregate guarantees only the aggregate value; use window ordering and filter the selected row when those columns are required.

Window-function rewrites

Determine the series grouping columns and row order before applying these patterns:

-- DIFFERENCE(value)
value - lag(value) OVER (PARTITION BY host ORDER BY ts)

-- CUMULATIVE_SUM(value)
sum(value) OVER (
  PARTITION BY host
  ORDER BY ts
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- MOVING_AVERAGE(value, 5)
avg(value) OVER (
  PARTITION BY host
  ORDER BY ts
  ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
)

DERIVATIVE, NON_NEGATIVE_DERIVATIVE, and ELAPSED must account for the time unit, the first-row NULL, and negative-value behavior. Use a CTE to calculate lag(value) and lag(ts) before deriving the result. Do not use an unverified one-line replacement.

Functions requiring dedicated rewrites

These functions have no safe, general one-to-one mapping:

• INTEGRAL
• MODE
• TOP / BOTTOM
• SAMPLE
• HISTOGRAM
• HOLT_WINTERS
• technical analysis functions

TOP and BOTTOM can often use row_number() OVER (PARTITION BY ... ORDER BY value DESC|ASC) with an outer filter. First confirm the InfluxQL tag arguments, returned columns, and tie behavior.

Regex and identifiers

Rewrite a value regex as follows:

-- InfluxQL
"host" =~ /^web-[0-9]+$/

-- GreptimeDB SQL
regexp_like(host, '^web-[0-9]+$')

Use NOT regexp_like(...) for a negative regex. When InfluxQL uses regex to select columns, measurements, or tag keys in SELECT, FROM, or GROUP BY, expand the matching objects first. Use UNION ALL for multiple schema-compatible tables; otherwise require an explicit mapping.

Preserve identifier quoting when required. Use single quotes for SQL strings and double quotes for identifiers.

Clauses without direct equivalents

• SLIMIT and SOFFSET limit series, while SQL LIMIT and OFFSET limit result rows. Define the series key first, then use dense_rank or a distinct-series CTE.
• tz() changes the timezone offset returned by InfluxQL. Set the GreptimeDB connection session timezone instead of silently dropping the clause.
• InfluxQL applies LIMIT per series, which may differ from a global SQL LIMIT.
• SELECT INTO writes data and is outside a read-only conversion. Generate INSERT INTO ... SELECT only when the user explicitly requests it.
• Metadata statements such as SHOW SERIES and SHOW TAG KEYS are not regular SELECT conversions. Design them separately against GreptimeDB information schema.

Example

Input:

SELECT MEAN("usage_user"), MAX("usage_system")
FROM "cpu"
WHERE "host" =~ /^web-/ AND time >= now() - 6h
GROUP BY time(10m), "host" fill(previous)
ORDER BY time DESC
LIMIT 100

Output, assuming ts is the time index:

SELECT
  ts,
  host,
  avg(usage_user) RANGE '10m' FILL PREV AS mean_usage_user,
  max(usage_system) RANGE '10m' FILL PREV AS max_usage_system
FROM cpu
WHERE regexp_like(host, '^web-')
  AND ts >= now() - '6 hours'::INTERVAL
ALIGN '10m' BY (host)
ORDER BY ts DESC
LIMIT 100;

Assumptions:

• cpu is the target table, ts is its time index, and host is the complete series key.

Semantic differences:

• FILL PREV fills only between each series' first and last data-bearing windows; it does not emit leading or trailing windows across the complete query time range.
• SQL LIMIT 100 applies to the complete result, while InfluxQL applies LIMIT 100 per series. Use row_number() OVER (PARTITION BY host ORDER BY ts DESC) and filter by row number when exact per-series limiting is required.

Sources

• GreptimeDB migration from InfluxDB: https://docs.greptime.com/user-guide/migrate-to-greptimedb/migrate-from-influxdb/
• GreptimeDB SQL guide: https://docs.greptime.com/user-guide/query-data/sql/
• GreptimeDB Range Query: https://docs.greptime.com/reference/sql/range/
• GreptimeDB SQL functions: https://docs.greptime.com/reference/sql/functions/overview/
• InfluxQL specification: https://docs.influxdata.com/influxdb/v2/reference/syntax/influxql/spec/
• InfluxQL functions: https://docs.influxdata.com/influxdb/v2/query-data/influxql/functions/