QueryBuilder

Supposing that you have a measurement h2o_feet:

> SELECT * FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The basic SELECT statement

Issue simple select statements

Query query = select().from(DATABASE,"h2o_feet");

SELECT * FROM "h2o_feet"

Select specific tags and fields from a single measurement

Query query = select("level description","location","water_level").from(DATABASE,"h2o_feet");

SELECT "level description",location,water_level FROM h2o_feet;

Select specific tags and fields from a single measurement, and provide their identifier type

Query query = select().column("\"level description\"::field").column("\"location\"::tag").column("\"water_level\"::field").from(DATABASE,"h2o_feet");

SELECT "level description"::field,"location"::tag,"water_level"::field FROM h2o_feet;

Select all fields from a single measurement

Query query = select().raw("*::field").from(DATABASE,"h2o_feet");

SELECT *::field FROM h2o_feet;

Select a specific field from a measurement and perform basic arithmetic

Query query = select().op(op(cop("water_level",MUL,2),"+",4)).from(DATABASE,"h2o_feet");

SELECT (water_level * 2) + 4 FROM h2o_feet;

Select all data from more than one measurement

Query query = select().from(DATABASE,"\"h2o_feet\",\"h2o_pH\"");

SELECT * FROM "h2o_feet","h2o_pH";

Select all data from a fully qualified measurement

Query query = select().from(DATABASE,"\"NOAA_water_database\".\"autogen\".\"h2o_feet\"");

SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet";

Select data that have specific field key-values

Query query = select().from(DATABASE,"h2o_feet").where(gt("water_level",8));

SELECT * FROM h2o_feet WHERE water_level > 8;

Select data that have a specific string field key-value

Query query = select().from(DATABASE,"h2o_feet").where(eq("level description","below 3 feet"));

SELECT * FROM h2o_feet WHERE "level description" = 'below 3 feet';

Select data that have a specific field key-value and perform basic arithmetic

Query query = select().from(DATABASE,"h2o_feet").where(gt(cop("water_level",ADD,2),11.9));

SELECT * FROM h2o_feet WHERE (water_level + 2) > 11.9;

Select data that have a specific tag key-value

Query query = select().column("water_level").from(DATABASE,"h2o_feet").where(eq("location","santa_monica"));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica';

Select data that have specific field key-values and tag key-values

Query query = select().column("water_level").from(DATABASE,"h2o_feet")
                .where(neq("location","santa_monica"))
                .andNested()
                .and(lt("water_level",-0.59))
                .or(gt("water_level",9.95))
                .close();

SELECT water_level FROM h2o_feet WHERE location <> 'santa_monica' AND (water_level < -0.59 OR water_level > 9.95);

Select data that have specific timestamps

Query query = select().from(DATABASE,"h2o_feet")
                .where(gt("time",subTime(7,DAY)));

SELECT * FROM h2o_feet WHERE time > now() - 7d;

The GROUP BY clause

Group query results by a single tag

Query query = select().mean("water_level").from(DATABASE,"h2o_feet") .groupBy("location");

SELECT MEAN(water_level) FROM h2o_feet GROUP BY location;

Group query results by more than one tag

Query query = select().mean("index").from(DATABASE,"h2o_feet")
                .groupBy("location","randtag");

SELECT MEAN(index) FROM h2o_feet GROUP BY location,randtag;

Group query results by all tags

Query query = select().mean("index").from(DATABASE,"h2o_feet")
                .groupBy(raw("*"));

SELECT MEAN(index) FROM h2o_feet GROUP BY *;

GROUP BY time interval

Group query results into 12 minute intervals

Query query = select().count("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z'"))
                .groupBy(time(12l,MINUTE));

SELECT COUNT(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'' GROUP BY time(12m);

Group query results into 12 minutes intervals and by a tag key

        Query query = select().count("water_level").from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z'"))
                .groupBy(time(12l,MINUTE),"location");

SELECT COUNT(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'' GROUP BY time(12m),location;

Advanced GROUP BY time() syntax

Group query results into 18 minute intervals and shift the preset time boundaries forward

Query query = select().mean("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:06:00Z"))
                .and(lte("time","2015-08-18T00:54:00Z"))
                .groupBy(time(18l,MINUTE,6l,MINUTE));

SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,6m);

Group query results into 12 minute intervals and shift the preset time boundaries back

Query query = select().mean("water_level").from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:06:00Z"))
                .and(lte("time","2015-08-18T00:54:00Z"))
                .groupBy(time(18l,MINUTE,-12l,MINUTE));

SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,-12m);

GROUP BY time intervals and fill()

Query select = select()
                .column("water_level")
                .from(DATABASE, "h2o_feet")
                .where(gt("time", op(ti(24043524l, MINUTE), SUB, ti(6l, MINUTE))))
                .groupBy("water_level")
                .fill(100);

SELECT water_level FROM h2o_feet WHERE time > 24043524m - 6m GROUP BY water_level fill(100);"

The INTO clause

Rename a database

Query select = select()
                .into("\"copy_NOAA_water_database\".\"autogen\".:MEASUREMENT")
                .from(DATABASE, "\"NOAA_water_database\".\"autogen\"./.*/")
                .groupBy(new RawText("*"));

SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *;

Write the results of a query to a measurement

Query select = select().column("water_level").into("h2o_feet_copy_1").from(DATABASE,"h2o_feet").where(eq("location","coyote_creek"));

SELECT water_level INTO h2o_feet_copy_1 FROM h2o_feet WHERE location = 'coyote_creek';

Write aggregated results to a measurement

Query select = select()
                .mean("water_level")
                .into("all_my_averages")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","coyote_creek"))
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:30:00Z"))
                .groupBy(time(12l,MINUTE));

SELECT MEAN(water_level) INTO all_my_averages FROM h2o_feet WHERE location = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m);

Write aggregated results for more than one measurement to a different database (downsampling with backreferencing)

Query select = select()
                .mean(raw("*"))
                .into("\"where_else\".\"autogen\".:MEASUREMENT")
                .fromRaw(DATABASE, "/.*/")
                .where(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:06:00Z"))
                    .groupBy(time(12l,MINUTE));

SELECT MEAN(*) INTO "where_else"."autogen".:MEASUREMENT FROM /.*/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:06:00Z' GROUP BY time(12m);

ORDER BY time DESC

Return the newest points first

Query select = select().from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .orderBy(desc());

SELECT * FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time DESC;

Return the newest points first and include a GROUP BY time() clause

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(time(12l,MINUTE))
                .orderBy(desc());

SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY time(12m) ORDER BY time DESC;

The LIMIT clause

Limit the number of points returned

Query select = select("water_level","location")
                .from(DATABASE,"h2o_feet").limit(3);

SELECT water_level,location FROM h2o_feet LIMIT 3;

Limit the number points returned and include a GROUP BY clause

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .limit(2);

SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) LIMIT 2;

The SLIMIT clause

Limit the number of series returned

Query select = select().column("water_level")
                .from(DATABASE,"h2o_fleet")
                .groupBy(raw("*"))
                .sLimit(1);

SELECT water_level FROM "h2o_feet" GROUP BY * SLIMIT 1

Limit the number of series returned and include a GROUP BY time() clause

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .sLimit(1);

SELECT water_level FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) SLIMIT 1;

The OFFSET clause

Paginate points

Query select = select("water_level","location").from(DATABASE,"h2o_feet").limit(3,3);

SELECT water_level,location FROM h2o_feet LIMIT 3 OFFSET 3;

The SOFFSET clause

Paginate series and include all clauses

Query select = select().mean("water_level")
                .from(DATABASE,"h2o_feet")
                .where()
                .and(gte("time","2015-08-18T00:00:00Z"))
                .and(lte("time","2015-08-18T00:42:00Z"))
                .groupBy(raw("*"),time(12l,MINUTE))
                .orderBy(desc())
                .limit(2,2)
                .sLimit(1,1);

SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1;

The Time Zone clause

Return the UTC offset for Chicago’s time zone

Query select = select()
                .column("test1")
                .from(DATABASE, "h2o_feet")
                .groupBy("test2", "test3")
                .sLimit(1)
                .tz("America/Chicago");

SELECT test1 FROM foobar GROUP BY test2,test3 SLIMIT 1 tz('America/Chicago');

Time Syntax

Specify a time range with RFC3339 date-time strings

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time","2015-08-18T00:00:00.000000000Z"))
                .and(lte("time","2015-08-18T00:12:00Z"));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z';

Specify a time range with second-precision epoch timestamps

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",ti(1439856000l,SECOND)))
                .and(lte("time",ti(1439856720l,SECOND)));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= 1439856000s AND time <= 1439856720s;

Perform basic arithmetic on an RFC3339-like date-time string

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",op("2015-09-18T21:24:00Z",SUB,ti(6l,MINUTE))));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-09-18T21:24:00Z' - 6m;

Perform basic arithmetic on an epoch timestamp

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",op(ti(24043524l,MINUTE),SUB,ti(6l,MINUTE))));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= 24043524m - 6m;

Specify a time range with relative time

Query select = select().column("water_level")
                .from(DATABASE,"h2o_feet")
                .where(eq("location","santa_monica"))
                .and(gte("time",subTime(1l,HOUR)));

SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' AND time >= now() - 1h;

Regular expressions

Use a regular expression to specify field keys and tag keys in the SELECT clause

Query select = select().regex("l").from(DATABASE,"h2o_feet").limit(1);

SELECT /l/ FROM h2o_feet LIMIT 1;

Use a regular expression to specify field keys with a function in the SELECT clause

Query select = select().regex("l").distinct().from(DATABASE,"h2o_feet").limit(1);

SELECT DISTINCT /l/ FROM h2o_feet LIMIT 1;

Use a regular expression to specify measurements in the FROM clause

Query select = select().mean("degrees").fromRaw(DATABASE,"/temperature/");

SELECT MEAN(degrees) FROM /temperature/;

Use a regular expression to specify a field value in the WHERE clause

Query select = select().regex("/l/").from(DATABASE,"h2o_feet").where(regex("level description","/between/")).limit(1);

SELECT /l/ FROM h2o_feet WHERE "level description" =~ /between/ LIMIT 1;

Use a regular expression to specify tag keys in the GROUP BY clause

Query select = select().regex("/l/").from(DATABASE,"h2o_feet").where(regex("level description","/between/")).groupBy(raw("/l/")).limit(1);

SELECT /l/ FROM h2o_feet WHERE "level description" =~ /between/ GROUP BY /l/ LIMIT 1;

Function with no direct implementation can be supported by raw expressions

Query select = select().raw("an expression on select").from(dbName, "cpu").where("an expression as condition");

SELECT an expression on select FROM h2o_feet WHERE an expression as condition;

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

QUERY_BUILDER.md

QUERY_BUILDER.md

QueryBuilder

The basic SELECT statement

The GROUP BY clause

GROUP BY time interval

Advanced GROUP BY time() syntax

GROUP BY time intervals and fill()

The INTO clause

ORDER BY time DESC

The LIMIT clause

The SLIMIT clause

The OFFSET clause

The SOFFSET clause

The Time Zone clause

Time Syntax

Regular expressions

Files

QUERY_BUILDER.md

Latest commit

History

QUERY_BUILDER.md

File metadata and controls

QueryBuilder

The basic SELECT statement

The GROUP BY clause

GROUP BY time interval

Advanced GROUP BY time() syntax

GROUP BY time intervals and fill()

The INTO clause

ORDER BY time DESC

The LIMIT clause

The SLIMIT clause

The OFFSET clause

The SOFFSET clause

The Time Zone clause

Time Syntax

Regular expressions