Time series generator in psql

time-series.sql

/**
  Time series generator.  This generates a series of times (as seconds since
  the epoch) between any two start and end time values (also seconds since
  the epoch).  The $1 value can be 'second', 'minute', 'hour', 'day', 'week', or
  'month'.  The $2 and $3 values are start and end time respectively (in seconds
  since the epoch.
 */ 
select date_part('epoch', date_trunc($1, to_timestamp($2)) + concat(tmp.index, $1)::interval) as time from generate_series(
  0,
  case
    when $1 = 'month' then (
      extract(
        year from age( 
          date_trunc('month', to_timestamp($3)), 
          date_trunc('month', to_timestamp($2)) 
        ) 
      )*12 + 
      extract( 
        month from age( 
          date_trunc('month', to_timestamp($3)), 
          date_trunc('month', to_timestamp($2)) 
        ) 
      ) 
    )::integer 
    when $1 = 'week' then ( 
      ( 
        date(date_trunc('week', to_timestamp($3))) - 
        date(date_trunc('week', to_timestamp($2))) 
      ) / 7 
    )::integer 
    when $1 = 'day' then ( 
      date(to_timestamp($3)) - date(to_timestamp($2)) 
    )::integer 
    when $1 = 'hour' then ( 
      ( 
        extract(epoch from date_trunc('hour', to_timestamp($3))) - 
        extract(epoch from date_trunc('hour', to_timestamp($2))) 
      ) / 60 / 60 
    )::integer 
    when $1 = 'minute' then ( 
      ( 
        extract(epoch from date_trunc('minute', to_timestamp($3))) - 
        extract(epoch from date_trunc('minute', to_timestamp($2))) 
      ) / 60 
    )::integer 
    when $1 = 'second' then ( 
      $3 - $2 
    )::integer 
  end 
) as tmp(index);