Can my DB?

-- Integer series
SELECT generate_series(1, 10) AS n;

-- Date series (one row per day in 2024)
SELECT gs::date AS day
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-12-31'::timestamp,
  '1 day'::interval
) AS gs;

-- Monthly series
SELECT generate_series(
  '2024-01-01'::timestamp,
  '2024-12-01'::timestamp,
  '1 month'::interval
)::date AS month_start;

-- Left join to fill gaps in time-series data
SELECT gs::date AS day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series('2024-01-01'::timestamp, '2024-01-31'::timestamp, '1 day') AS gs
LEFT JOIN orders o ON DATE(o.created_at) = gs::date
GROUP BY gs::date
ORDER BY 1;

• Works for integers, numeric, timestamp, and timestamptz types.
• Interval step supports complex intervals: '1 month', '7 days', '6 hours', etc.
• Set-returning function; can be used directly in a FROM clause.
• Timestamptz variant handles daylight saving transitions correctly when using '1 day' step.
• Multiple generate_series() calls in the same FROM clause produce a cross join (all combinations).

-- Recursive CTE workaround (MySQL 8.0+)
WITH RECURSIVE date_series AS (
  SELECT '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL 1 DAY
  FROM date_series
  WHERE day < '2024-12-31'
)
SELECT day FROM date_series;

• No GENERATE_SERIES function.
• Recursive CTE is the standard workaround for date series (MySQL 8.0+).
• Performance may degrade for very long series; consider a permanent numbers/dates table.
• Recursive CTE requires setting cte_max_recursion_depth for series longer than 1000.

-- Integer sequence using seq virtual table
SELECT seq FROM seq_1_to_10;

-- Date series using seq_0_to_n
SELECT DATE('2024-01-01') + INTERVAL seq DAY AS day
FROM seq_0_to_364;

-- Monthly series
SELECT DATE('2024-01-01') + INTERVAL seq MONTH AS month_start
FROM seq_0_to_11;

-- Recursive CTE also works (MariaDB 10.2+)
WITH RECURSIVE date_series AS (
  SELECT '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL 1 DAY FROM date_series WHERE day < '2024-12-31'
)
SELECT day FROM date_series;

• MariaDB provides seq_0_to_n and seq_1_to_n virtual tables for instant integer sequences.
• Sequence tables are unique to MariaDB; they are not available in MySQL.
• Date series: combine a seq table with DATE arithmetic.
• Sequences up to 2^64-1 are supported (e.g., seq_0_to_18446744073709551615).
• Recursive CTEs also available as an alternative (MariaDB 10.2+).

-- CONNECT BY workaround (classic Oracle)
SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 AS day
FROM DUAL
CONNECT BY LEVEL <= 366;

-- Date series for 2024
SELECT DATE '2024-01-01' + LEVEL - 1 AS day
FROM DUAL
CONNECT BY DATE '2024-01-01' + LEVEL - 1 <= DATE '2024-12-31';

-- Recursive CTE (Oracle 11.2+)
WITH date_series(day) AS (
  SELECT DATE '2024-01-01' FROM DUAL
  UNION ALL
  SELECT day + 1 FROM date_series
  WHERE day < DATE '2024-12-31'
)
SELECT day FROM date_series;

• No GENERATE_SERIES function.
• CONNECT BY LEVEL is the classic Oracle idiom for generating series.
• Recursive CTE workaround available in Oracle 11.2+.
• A pre-populated numbers or calendar table is a common permanent solution.

-- Recursive CTE workaround for date series
WITH date_series AS (
  SELECT CAST('2024-01-01' AS DATE) AS day
  UNION ALL
  SELECT DATEADD(day, 1, day)
  FROM date_series
  WHERE day < '2024-12-31'
)
SELECT day FROM date_series
OPTION (MAXRECURSION 0);

-- SQL Server 2022: GENERATE_SERIES for integers only
SELECT DATEADD(day, value, '2024-01-01') AS day
FROM GENERATE_SERIES(0, 365);

• No built-in date series function.
• SQL Server 2022 added GENERATE_SERIES for integers only: GENERATE_SERIES(start, stop [, step]).
• For date series with SQL Server 2022: add integer series to a base date with DATEADD.
• Recursive CTE with OPTION (MAXRECURSION 0) for large series (default 100 limit).
• A permanent tally/numbers table is a widely recommended performance pattern.

-- Recursive CTE workaround
WITH RECURSIVE date_series(day) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT date(day, '+1 day')
  FROM date_series
  WHERE day < '2024-12-31'
)
SELECT day FROM date_series;

-- Integer series
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;

• No GENERATE_SERIES function.
• Recursive CTE is the standard workaround.
• date(day, '+1 day') is SQLite's date arithmetic syntax.
• The generate_series() function is available in some SQLite builds (e.g., with the series.c extension).
• SQLITE_MAX_LENGTH limits how large recursive CTEs can grow.

-- Integer series
SELECT generate_series(1, 10) AS n;

-- Date series
SELECT (DATE '2024-01-01' + gs.i * INTERVAL '1 day')::date AS day
FROM generate_series(0, 364) AS gs(i);

-- Timestamp series
SELECT generate_series(
  '2024-01-01 00:00:00'::timestamp,
  '2024-01-31 23:00:00'::timestamp,
  '1 hour'::interval
) AS hour_start;

• GENERATE_SERIES matches PostgreSQL syntax for integers and timestamps.
• Useful for building date/time dimension tables in ETL workflows.
• Timestamp variant with interval step supported.
• Must be run on the leader node; not directly distributable across slices.

-- Recursive CTE workaround (DB2 9.7+)
WITH date_series(day) AS (
  SELECT DATE '2024-01-01' FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT day + 1 DAY FROM date_series
  WHERE day < DATE '2024-12-31'
)
SELECT day FROM date_series;

• No GENERATE_SERIES function.
• Recursive CTE workaround is standard.
• DB2 date arithmetic uses the + 1 DAY syntax.
• Cross join against syscat.columns (large system table) can generate integers without recursion.

-- GENERATOR produces integer rows; use date arithmetic for dates
SELECT DATEADD('day',
         ROW_NUMBER() OVER (ORDER BY seq4()) - 1,
         '2024-01-01'::DATE) AS day
FROM TABLE(GENERATOR(ROWCOUNT => 366));

-- Monthly series
SELECT DATEADD('month',
         ROW_NUMBER() OVER (ORDER BY seq4()) - 1,
         '2024-01-01'::DATE) AS month_start
FROM TABLE(GENERATOR(ROWCOUNT => 12));

-- Integer series using SEQ1()
SELECT SEQ1() AS n
FROM TABLE(GENERATOR(ROWCOUNT => 100));

• GENERATOR(ROWCOUNT => n) table function generates n rows.
• SEQ1(), SEQ2(), SEQ4(), SEQ8() produce monotonically increasing integers.
• ROW_NUMBER() OVER (ORDER BY SEQ4()) provides a 1-based row number for date arithmetic.
• No direct date series function; GENERATOR + DATEADD is the idiom.
• GENERATOR is a Snowflake-specific feature not found in other databases.

-- Date series
SELECT day
FROM UNNEST(
  GENERATE_DATE_ARRAY('2024-01-01', '2024-12-31', INTERVAL 1 DAY)
) AS day;

-- Monthly series
SELECT month_start
FROM UNNEST(
  GENERATE_DATE_ARRAY('2024-01-01', '2024-12-01', INTERVAL 1 MONTH)
) AS month_start;

-- Integer array
SELECT n FROM UNNEST(GENERATE_ARRAY(1, 100)) AS n;

-- Timestamp series
SELECT ts
FROM UNNEST(
  GENERATE_TIMESTAMP_ARRAY(
    '2024-01-01 00:00:00 UTC',
    '2024-01-01 23:00:00 UTC',
    INTERVAL 1 HOUR
  )
) AS ts;

• GENERATE_ARRAY(start, end [, step]) produces an ARRAY of INT64 or FLOAT64.
• GENERATE_DATE_ARRAY(start, end [, INTERVAL n unit]) produces an ARRAY of DATE.
• GENERATE_TIMESTAMP_ARRAY produces an ARRAY of TIMESTAMP.
• Arrays must be unnested with UNNEST() to use as rows in a query.
• All three functions return ARRAYs, not sets; they require UNNEST, unlike PostgreSQL.