Generate_Series() Equivalent in MySQL

generate_series() equivalent in MySQL

This is how I do it. It creates a range of dates from 2011-01-01 to 2011-12-31:

select 
    date_format(
        adddate('2011-1-1', @num:=@num+1), 
        '%Y-%m-%d'
    ) date
from 
    any_table,    
    (select @num:=-1) num
limit 
    365

-- use limit 366 for leap years if you're putting this in production

The only requirement is that the number of rows in any_table should be greater or equal to the size of the needed range (>= 365 rows in this example). You will most likely use this as a subquery of your whole query, so in your case any_table can be one of the tables you use in that query.

generate_series in MySQL

Here is the concept, but I don't have mySQL installed on this box. You will need to create a table of integers, using AUTO INCREMENT. A table of numbers is generally a handy table to have available in a database, and would only need be created once

create table NumberList (id MEDIUMINT NOT NULL AUTO_INCREMENT,fill char(1))

declare @x INT
set @x=0
while @x < 20
begin
    insert into numberList values(null)
    Set @x = @x+1
end 

Then, join this table as shown below using the LIMIT clause

select substr('somestring',id) 
from numberlist
limit len('somestring')

I wrote this in SQL server, but it shouldn't be too difficult to convert to mySQL...

The code below SHOULD work in mySQL

DECLARE xx INT DEFAULT 0;
  WHILE xx < 20 DO
    insert into numberList values(null)        
    SET xx = xx + 1;
 END WHILE;

Generate an integer sequence in MySQL

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.

How do I make a row generator in MySQL?

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

In Oracle:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL (up to 100 rows):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

or using hint up to 32768

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < 32768
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint

In PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

In MySQL, nothing.

Generating time series reports

Let's say you have a gigantic table measure with two columns datestamp and temp.

Let's say you want to see the temperature every six minutes (10x per hour) for the last week. You can do this sort of thing. We'll get to defining trunc in a moment.

  SELECT trunc(datestamp) datestamp, AVG(temp) temp
    FROM measure
   WHERE datestamp >= CURDATE() - INVERVAL 7 DAY
  GROUP BY trunc(datestamp)
  ORDER BY trunc(datestamp)

That works for any reasonable definition of trunc. In this case trunc(t) returns the beginning of the six-minute period in which t occurs. So, trunc('1942-12-07 08:45:17') gives 1942-12-07 08:42:00).

Here's a query that works for every six minute interval.

  SELECT DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') +
            INTERVAL (MINUTE(datestamp) -
                      MINUTE(datestamp) MOD 6) datestamp, 
         AVG(temp) temp
    FROM measure
   WHERE datestamp >= CURDATE() - INVERVAL 7 DAY
   GROUP BY DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') +
            INTERVAL (MINUTE(datestamp) -
                      MINUTE(datestamp) MOD 6)
  ORDER BY 1

This uses inbuilt date arithmetic rather than unix timestamp arithmetic.

You can use a stored function to make this easier to read.

DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_N_MINUTES$$
CREATE
  FUNCTION TRUNC_N_MINUTES(datestamp DATETIME, n INT)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'truncate to N minute boundary. For example,
           TRUNCATE_N_MINUTES(sometime, 15) gives the nearest
           preceding quarter hour'
  RETURN DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') +
                INTERVAL (MINUTE(datestamp) -
                          MINUTE(datestamp) MOD n) MINUTE$$
DELIMITER ;

Then your query will say

  SELECT TRUNC_N_MINUTES(datestamp, 6) datestamp, AVG(temp) temp
    FROM measure
   WHERE datestamp >= CURDATE() - INVERVAL 7 DAY
  GROUP BY TRUNC_N_MINUTES(datestamp, 6)
  ORDER BY TRUNC_N_MINUTES(datestamp, 6)

If you want to summarize by 5, 10, 15, or minute boundaries (three items per hour) simply use that number in place of 6.

You'll need different trunc() functions for hours, etc.

The trunc() function for daily summaries is DATE(datestamp).
For monthly summaries it is LAST_DAY(datestamp). For example,

  SELECT LAST_DAY(datestamp) month_ending, AVG(temp) temp
    FROM measure
  GROUP BY LAST_DAY(datestamp) 
  ORDER BY LAST_DAY(datestamp) 

yields a month-by-month summary.

---

Related Topics