![]() ![]() To do this, we utilize a standard feature of relational databases and SQL. Now that we understand how to use generate_series(), how do we create some time-series data to insert into TimescaleDB for testing and visualization? (17 rows) How to generate time-series data For example, if we modify the step INTERVAL above to '1 hour 25 minutes', the result only returns 17 rows, the last of which is before the stop value. However, if the step interval resulted in the stop value being skipped over, it will not be included in your output. As long as the INTERVAL can increment evenly up to the stop date, it will be included. The reason we got 25 rows (representing 25 hours rather than 24 as you might expect) is that the stop value can be reached using the equal one-hour INTERVAL (the step parameter). ![]() Notice that the returned dates are inclusive of the start and stop values, just as we saw with the numeric example before. The only difference is that the third parameter, the step INTERVAL used to increment the date, is required for date generation, as shown here: SELECT * from generate_series( Using generate_series() to produce a range of dates is equally straightforward. However, there is an optional third parameter that can be used to specify the increment length, known as the step parameter.įor example, if we wanted to generate rows that counted by two from 0 to 10, we could use this SQL instead: SELECT * from generate_series(0,10,2) ġ0 PostgreSQL generate_series() with dates When used to generate numeric data, generate_series() will increment the values by 1. This will produce output that looks like this: generate_series|įrom this first example, we can see that generate_series() returns sequential numbers between a start parameter and stop parameter. The function is simple and easy to get started with, taking at least two required arguments to specify the start and stop parameters for the generated data. The PostgreSQL documentation calls it a Set Returning Function because it can return more than one row. Generate_series() is a built-in PostgreSQL function that makes it easy to create ordered tables of numbers or dates. Ways to create complex time-series data, using additional PostgreSQL math functions and JOINs.īy the end of this series, you'll be ready to test almost any PostgreSQL or TimescaleDB feature, create quick datasets for general testing, meetup presentations, demos, and more! Intro to PostgreSQL generate_series().How to create more realistic looking time-series data with custom PostgreSQL functions.What PostgreSQL generate_series() is and how to use it for basic data generation (similar to our tutorial to Simulate IoT Data).In this three-part blog series, we'll go through a few ways to use the generate_series() function to create large datasets in PostgreSQL, including: We also see users in our community Slack asking how they can test a feature or decide if their data is a good fit for TimescaleDB.Īlthough using real data from your current application would be great (and ideal), knowing how to quickly create a representative time-series dataset using varying cardinalities and different lengths of time, is a helpful - and advantageous - skill to have.įortunately PostgreSQL provides a built-in function to help us create this sample data using the SQL that we already know and love - no external tools required. As the makers of TimescaleDB, we often need to quickly create lots of sample time-series data to demonstrate a new database feature, run a benchmark, or talk about use cases internally.
0 Comments
Leave a Reply. |