Published at Mar 6, 2024
Query LanguagesWritten by Carlota Soto
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.
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 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 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.
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.
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.
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.
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.
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.
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.
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.
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:
from(bucket: "my-bucket")
|> range(start: -24h)
|> filter(fn: (r) => r._measurement == "temperature" and r._field == "value")
|> aggregateWindow(every: 1h, fn: mean)
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.
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 |
|
|
Filter |
|
|
Group by |
|
|
Order by |
|
|
Limit |
|
|
Simple aggregates |
|
|
Count |
|
|
Sum |
|
|
Min/max |
|
|
Between time interval |
|
|
Specific time interval |
| Only in Timescale
|
Filter by value |
|
|
Join | Not directly supported, use subqueries or merge series |
|
Having | No direct equivalent |
|
Continuous queries/continuous aggregates—only in Timescale (not available in native PostgreSQL) | ||
Create |
|
|
Drop |
|
|
List |
|
|
Inserting data | ||
Single row |
|
|
Multiple rows | Use multiple INSERT INTO statements or concatenate values with newline characters |
|
Deleting data | ||
Specific records |
|
|
Retention policies—only in Timescale (not available in native PostgreSQL) | ||
Create |
|
|
Modify |
| No direct modification, recreate the policy or run |
List |
|
|
Compression—only in Timescale (not available in native PostgreSQL) | ||
Enable/disable | Not natively supported, relies on underlying storage mechanisms |
|
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.
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.
InfluxDB is a NoSQL database specifically designed for time-series data.
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.
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.
InfluxQL uses SQL-like syntax, while Flux has a functional programming style.
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.
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.
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.
If you are not using TimescaleDB yet, take a look. If you're already running a PostgreSQL database on your own hardware, you can simply add the TimescaleDB extension. If you prefer to try Timescale in AWS, create a free account on our platform. It only takes a couple of seconds, no credit card required.