Arel: select from function

Question

Consider the following query:

SELECT DATE_TRUNC('hour', date_range) 
FROM GENERATE_SERIES(:start_date, :end_date, :interval) as date_range

Is it possible to use GENERATE_SERIES(...) as a table (data source)? Ideally, it would look like this:

t = series(start, end, as: 'date_range')
dt = Arel::Nodes::NamedFunction.new('DATE_TRUNC', ['hour', t[:date_range]])
t.project(dt)

Upd1. Why do I need GENERATE_SERIES? I have some data that I need to process with regards to timestamps this data was available on, and output it as a 2D plot. As a simple example consider a clickstream = (id, created_at). I want to plot a number of clicks made prior to a certain date with regards to a given datetime grid (i.e Nov 17, Nov 18, Nov 19, ..., Nov 30). And the thing is I want this all happen inside PostgreSQL.

Upd2. Example query without variables could look like this:

SELECT DATE_TRUNC('hour', date_range) FROM GENERATE_SERIES('2015-01-01 00:15:38'::TIMESTAMP, '2015-01-10 23:59:59'::TIMESTAMP, '1 HOUR') as date_range;

Answer 1

One way is to simply build all the necessary parts of the AST manually

def timestamp(ts)
  Arel::Nodes::NamedFunction.new(
    'CAST', [
      Arel::Nodes::As.new(
        Arel::Nodes.build_quoted(ts),
        Arel::Nodes::SqlLiteral.new('timestamp')
      )
    ]
  )
end

def series(from, to, by, options = {})
  Arel::Nodes::NamedFunction.new(
    'GENERATE_SERIES', [
      timestamp(from),
      timestamp(to),
      Arel::Nodes::SqlLiteral.new(by)
    ]
  ).as(options.fetch(:as, 'series'))
end

def date_trunc(by, attribute)
  Arel::Nodes::NamedFunction.new(
    'DATE_TRUNC', [Arel.sql("'#{by}'"), attribute]
  )
end

date_range = Arel::Table.new('date_range')
result = date_range.
  from(series(5.days.ago, 4.days.ago, "'1 hour'", as: 'date_range')).
  project(date_trunc('hour', date_range[:date_range]))

Test:

User.find_by_sql(result.to_sql).to_a.map &:attributes    

SELECT DATE_TRUNC('hour', "date_range"."date_range")
FROM GENERATE_SERIES(
  CAST('2015-02-03 21:29:22.729188' AS timestamp),
  CAST('2015-02-04 21:29:22.729633' AS timestamp),
  '1 hour'
) AS date_range

[{"date_trunc"=>2015-02-03 21:00:00 UTC, "id"=>nil},
 {"date_trunc"=>2015-02-03 22:00:00 UTC, "id"=>nil},
 {"date_trunc"=>2015-02-03 23:00:00 UTC, "id"=>nil},
 {"date_trunc"=>2015-02-04 00:00:00 UTC, "id"=>nil},
# ...
 {"date_trunc"=>2015-02-04 21:00:00 UTC, "id"=>nil}]