![]() ![]() In this example, we simply added data to the rows being returned from generate_series(). SELECT 'Hello Timescale!' as myStr, * FROM generate_series(1,5) You may have done this at some point with a string of text that needed to be repeated for each returned row. Therefore, just like when we select data from a regular table with SQL, we can add more columns of data using other functions or static values. Recall that generate_series() is a Set Returning Function that returns a "table" of data (a set) just as if we had selected it from a table. ![]() 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |