When users are looking to migrate from InfluxDB to TimescaleDB, they often ask questions about query languages. To help them out, in this article we provide an overview of the InfluxDB and TimescaleDB/PostgreSQL query languages and a cheatsheet to guide users in moving their code from InfluxQL to SQL.
InfluxQL, Flux, and SQL: An Overview
SQL
SQL, or Structured Query Language, is a universal language designed for managing and manipulating relational databases. Its history dates back to the 1970s when it was first developed by IBM, and it quickly became the gold standard for a variety of applications. Over the decades, SQL has been refined and expanded, leading to widespread global adoption.
SQL’s robustness is underscored by its ability to handle complex queries, transactions, and routine data management tasks with efficiency and reliability. It has become the foundational language for many popular RDBMS like PostgreSQL, MySQL, and Microsoft SQL Server, proving its resilience, flexibility, and enduring relevance in the ever-evolving landscape of data management and analytics.
The SQL implementation—in the particular context of PostgreSQL—sets itself apart by its adherence to SQL standards along with the incorporation of advanced features that extend beyond the standard SQL repertoire. These features include complex queries, foreign keys, triggers, views, and stored procedures, among others.
InfluxQL
InfluxQL is a query language specifically built for InfluxDB. InfluxQL has a SQL-like syntax, meaning it’s built to resemble SQL to make it easier for developers to learn and work with. InfluxQL is the language used in the 1.x version of InfluxDB.
The core strength of InfluxQL resides in its specialized functions and operators, designed to cater specifically to time-series data—including data filtering, aggregation, and transformation. The language also supports continuous queries and retention policies, enabling real-time data processing and efficient time-series data management.
Flux
Flux is a data scripting and query language developed by InfluxData, designed to handle queries and data analysis in the 2.x version of InfluxDB. Unlike InfluxQL, Flux is designed to access various data sources (including SQL databases and CSV files) aiming to facilitate integrated data analytics across diverse datasets.
Flux’s syntax and operational paradigms are quite different from InfluxQL or SQL. Flux is a functional language equipped with operators and functions intended for complex data transformations and analytical operations. It encompasses a range of functions intended for manipulating time series data, mathematical operations, and handling strings, among other tasks.
Query Language Limitations
SQL
SQL, while a robust and widely used query language, has some downsides. Time-series data presents unique challenges, including large data volumes, high ingestion rates, and the need for complex queries to analyze data across time intervals. Standard SQL can sometimes struggle with the efficiency and performance needed to manage and analyze extensive time-series datasets. Queries can become complex and computationally intensive, and the rigid schema structure of SQL can also pose challenges in scenarios where flexibility and adaptability to changing data structures are essential.
SQL’s design, rooted in the management of relational databases, isn't inherently tailored for working with time-series data. Operations like data rollups, downsampling, and retention policies, which are common in time-series data management, aren’t natively supported in native PostgreSQL—although this is mitigated by TimescaleDB, which adds many of these functionalities.
InfluxQL
While InfluxQL mirrors SQL in many respects, it doesn’t quite match the depth and versatility of SQL or other more advanced query languages. InfluxQL is specifically tailored for time-series data—therefore, its scope is inherently focused and limited. This constraint can be a bottleneck for users looking to perform complex data manipulations, transformations, and analytics that go beyond basic aggregations and filtering.
JOINs are the most clear example of this: while InfluxQL provides basic aggregations, grouping, and filtering, JOINs are not supported. Queries involving intricate calculations, data transformations, or joining multiple measurements can be cumbersome or, in some instances, unattainable with InfluxQL.
Flux
The first limitation of Flux is its learning curve, which can be a significant hurdle, especially for those already used to SQL or InfluxQL. Flux introduces a new syntax and functional programming style that may require a period of adjustment: its set of complex functionalities, operators, and expressions, while powerful, can be intricate and somewhat intimidating to use.
In terms of functionality, Flux is still maturing as a query language. As a relatively new language compared to established query languages like SQL, it lacks the extensive library of functions and broad community support that comes with longstanding languages. It is in continuous development, meaning users might encounter changes and updates that could impact existing scripts and applications. Documentation, examples, and community knowledge are less abundant than with more established languages like SQL.
Performance can also be a point of consideration. As Flux continues to evolve, enhancements in performance, optimization techniques, and resource management are still ongoing.
Key Differences
Syntax
SQL, in the context of PostgreSQL and other relational database systems, is characterized by its standardized syntax, which has been refined over decades. Its consistency and predictability are hallmarks that have contributed to SQL’s widespread adoption in various data management applications.
InfluxQL is crafted with a SQL-like syntax, a characteristic that is instrumental in offering a sense of familiarity and ease of use, especially for those who have previous experience with SQL. It’s straightforward, readable, and focuses on simplifying the query process for time-series data.
Flux diverges, embodying functional programming principles. This design choice lends Flux advanced data processing capabilities, allowing for more complex and varied operations. However, this also implies a steeper learning curve, especially for those not acquainted with functional programming.
Flexibility & extensibility
SQL shines in its ubiquity. It’s supported by a multitude of relational database systems, including PostgreSQL. Its universal structure and wide range of built-in functions make it a flexible tool for various data handling tasks.
InfluxQL is bespoke to InfluxDB and has been finely tuned to handle time-series data effectively. However, this specialization can also be a limitation as it isn’t designed to be as extensible or flexible for other types of data or databases.
Flux counters with versatility. It’s not just tethered to InfluxDB but has the architecture that allows it to be extended for use with other data sources, enhancing its utility in a diverse range of data processing and analytic applications.
Data handling capabilities
SQL brings to the table a broad range of functions and capabilities for data retrieval, manipulation, and analytics. In the context of PostgreSQL with TimescaleDB, SQL’s data handling capacities are augmented to effectively manage, analyze, and visualize time-series data with the reliability and efficiency characteristic of relational databases.
InfluxQL is renowned for its efficiency in querying time-series data. It’s optimized to handle queries that are specifically designed to analyze data points indexed by time, making it a go-to option for real-time analytics and monitoring applications.
Flux takes it a step further by offering enhanced data transformation and analytic capabilities. It’s equipped with a rich set of functions and operators that can handle complex analytics, transformations, and even machine learning tasks, showcasing its prowess in advanced data handling scenarios.
Analytical depth
SQL, especially when bolstered by extensions like TimescaleDB in PostgreSQL, offers a harmonious blend of reliability, efficiency, and analytical depth, supported by a mature ecosystem and a vast community of developers and users.
InfluxQL, while efficient, can sometimes be limiting in terms of analytical depth. It’s excellent for standard queries but can encounter challenges with more complex analytical tasks.
Flux is engineered to offer a deeper analytical dive. Its functional programming base allows for intricate computations, analytics, and data manipulations, enabling users to extract nuanced insights from their data.
Example Query
To bring this information home, let’s run through an example of querying the mean value of a field over a specified time period in InfluxQL, Flux, and SQL.
Let's say we are querying the mean temperature from a temperature measurement that has been recorded over the past 24 hours.
In InfluxQL, the query would be something like this:
SELECT
MEAN("value")
FROM
"temperature"
WHERE
time > now() - 24h
GROUP BY
time(1h)
This InfluxQL query calculates the mean value of the “value” field from the “temperature” measurement, where the time is greater than the current time minus 24 hours. The results are grouped in one-hour intervals.
In Flux, a similar query would look more like this:
In this Flux query, data is retrieved from “my-bucket”, and the range() function is used to filter data from the last 24 hours. The filter() function narrows down the data to the “temperature” measurement and the “value” field. The aggregateWindow() function is then used to calculate the mean value at one-hour intervals.
If we wanted to write a similar query SQL using PostgreSQL with TimescaleDB, we would use:
SELECT
time_bucket('1 hour', time) AS one_hour_interval,
AVG(value) as mean_temperature
FROM
temperature
WHERE
time > NOW() - INTERVAL '24 hours'
GROUP BY
one_hour_interval
ORDER BY
one_hour_interval;
In this SQL query, you can see the following:
We used the TimescaleDB function time_bucket to create buckets of one-hour intervals.
AVG(value) calculates the mean value of the temperature readings within each time bucket.
The WHERE clause filters the data to include only the rows where the time is within the past 24 hours.
The results are grouped by the one-hour intervals and ordered accordingly to present a chronological view of the mean temperature readings.
InfluxQL to SQL Cheatsheet for Common Operations
If you want to transfer some code from InfluxQL to SQL (or vice versa), this cheatsheet will help you.
[Take into account that this information is simplified (like in all cheatsheets). Always ensure to tailor the queries according to your specific database configuration and version, and always refer to the official documentation for detailed and accurate information.]
Database Operation
InfluxQL
SQL
Select data
SELECT * FROM “measurement”
SELECT * FROM measurement
Filter
WHERE time > now() - 1d
WHERE time > NOW() - INTERVAL 1 DAY
Group by
GROUP BY “tag”
GROUP BY column_name
Order by
ORDER BY time DESC
ORDER BY time DESC
Limit
LIMIT 10
LIMIT 10
Simple aggregates
SELECT MEAN(“value”) FROM “measurement”
SELECT AVG(column_value) FROM measurement
Count
SELECT COUNT(“value”) FROM “measurement”
SELECT COUNT(column_value) FROM measurement
Sum
SELECT SUM(“value”) FROM “measurement”
SELECT SUM(column_valye) FROM measurement
Min/max
SELECT MIN(“value”) FROM “measurement”
SELECT MIN(column_value) FROM measurement
Between time interval
WHERE time BETWEEN 'start_time' AND 'end_time'
WHERE time BETWEEN start_time AND end_time
Specific time interval
GROUP BY time(5m)
Only in Timescale
SELECT time_bucket('5 minutes', time)
Filter by value
WHERE(“value”) > 50
WHERE column_valye > 50
Join
Not directly supported, use subqueries or merge series
JOIN ON table1.column_bname = table2.column_name
Having
No direct equivalent
HAVING COUNT(column_name) > value
Continuous queries/continuous aggregates—only in Timescale (not available in native PostgreSQL)
Create
CREATE CONTINUOUS QUERY cq_name ON db_name BEGIN SELECT MEAN("value") INTO "target_measurement" FROM "source_measurement" GROUP BY time(1h) END
CREATE MATERIALIZED VIEW mat_view_name WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', time) as bucket, AVG(value) FROM measurement GROUP BY bucket;
Drop
DROP CONTINUOUS QUERY cq_name ON db_name
DROP MATERIALIZED VIEW mat_view_name;
List
SHOW CONTINUOUS QUERIES
\d+ (in psql, then search for materialized views)
Inserting data
Single row
INSERT INTO "measurement" (tag, value) VALUES ('tag_value', 30)
INSERT INTO measurement (time, tag, value) VALUES (NOW(), 'tag_value', 30);
Multiple rows
Use multiple INSERT INTO statements or concatenate values with newline characters
SELECT drop_chunks(INTERVAL '7 days', 'measurement'); (to drop older chunks)
Modify
ALTER RETENTION POLICY rp_name ON db_name DURATION 30d DEFAULT
No direct modification, recreate the policy or run drop_chunks() with a different interval
List
SHOW RETENTION POLICIES ON db_name
\d+ (in psql, then look for policies associated with hypertables)
Compression—only in Timescale (not available in native PostgreSQL)
Enable/disable
Not natively supported, relies on underlying storage mechanisms
ALTER TABLE measurement SET (timescaledb.compress, timescaledb.compress_segmentby = 'tag');` (to enable),
SELECT decompress_chunk(c) FROM show_chunks('measurement') AS c;` (to decompress specific chunks)
P.S. You might be wondering why we didn’t include Flux in the table above: Creating a cheatsheet to compare InfluxQL and SQL is relatively straightforward due to their similar syntax and structure. Even if these are only guidelines, we believe they can help you. However, incorporating Flux into the mix in a way that’s useful for you is harder because the syntax is so different. Direct comparisons between SQL and Flux are challenging.
Frequently Asked Questions
What query language does InfluxDB use?
InfluxDB uses two different query languages, InfluxQL and Flux, depending on the InfluxDB version. InfluxDB 1.x and InfluxDB 3.x use InfluxQL, while InfluxDB 2.x uses Flux.
Is InfluxDB a NoSQL database or SQL?
InfluxDB is a NoSQL database specifically designed for time-series data.
Does InfluxDB Use SQL?
No. InfluxDB 1.x and 3.x use InfluxQL, a SQL-like query language, for data querying and manipulation. InfluxDB 2.x uses Flux, another query language.
Does InfluxDB 2.0 use InfluxQL?
Not as the main language. InfluxDB 2.0 primarily uses Flux as its query language but also provides compatibility for InfluxQL to support existing queries and applications.
What is the main difference between InfluxQL and Flux?
InfluxQL uses SQL-like syntax, while Flux has a functional programming style.
What are the advantages of InfluxQL vs. SQL?
InfluxQL offers a simplified, SQL-like syntax optimized for querying and analyzing time-series data, making it more efficient for specific use cases like real-time analytics.
What are the disadvantages of InfluxQL vs. SQL?
InfluxQL has limited functionality and flexibility compared to SQL, making it less versatile for complex queries, for analyzing data across tables, or for integrations with other systems and tools.
Conclusion
In this article, we’ve explored the differences between InfluxQL, Flux, and SQL as query languages. While InfluxQL and Flux are query languages used by InfluxDB and have been purpose-built to work with time-series data, SQL is a query language widely used in relational databases, including PostgreSQL and TimescaleDB.
While InfluxQL and Flux are potent in their respective domains, the adaptability, maturity, and comprehensive nature of SQL, enhanced by TimescaleDB’s innovations, make it a compelling option for handling not just time-series data but a broad spectrum of data management and analytic requirements.
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale