Search code examples
rubypostgresqlsinatrasequel

Postgres 'to_char' in Sequel

I'm writing a Sinatra API using Sequel, but I don't know how to translate some of my postgres queries into Sequel. My table has a date column and I want to graph records grouped by year-month, so I have the following SQL:

year_months_sql = "select distinct to_char(date, 'YYYY-MM') as year_month
from receipts
where date >= ?
order by year_month asc"

This is one of a few queries that uses this to_char(date, 'YYYY-MM'). I can't find anything on Sequel docs about this.

Solution

  • to_char is an SQL function, and there are quite a few places in Sequel's documentation that discuss them, such as http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html#label-Functions

    Here's a translation of your SQL to Sequel's DSL:

    DB[:receipts].
  distinct.
  select{to_char(:date, 'YYYY-MM').as(:year_month)}.
  where{date >= ?}.
  order(:year_month)