functions

Functions are methods you can use to transform an SQL field (to get date year for example).

usage

Here is a simple example using “year” function:

posts in site where log_at.year = now.year - 1

You can also use functions on query parameters (rubyless evaluated):

posts where log_at.year = #{date}.year

dates

Available functions related to dates:

year, month, week, day, date

Please note that ‘day’ will not return the day of the month (such as “3”), but the UTC date without time (think “same day”). Since dates are stored as UTC, this might not be very useful. However, there is a special “date” macro:

“date” macro

This helps find events in a 24h interval. The two examples below are exactly equivalent:

The ”#{date}” part of the queries retrieves the view’s current date (passed as ?date=2011-04-20 in the url for example). See date.

nodes where #{date} >= updated_at and updated_at < #{date} + 1 day in site

With the “date” macro:

nodes where updated_at.date = #{date} in site

Dates are stored in UTC in the database but are converted from/to the visitor’s timezone when needed. This is why we cannot use SQL functions such as DATE() but we need to use intervals for calendars and other “day” related queries.

changed today

Finding nodes “changed today” can be done with:

nodes where updated_at > now - 24 hours in site

random

This function (not needing any field) can be used to sort for example):

images where updated_at > #{date} - 7 days order by random limit 5

numerical

min, max, sum, count

For example, to find elements with the same title:

nodes
  select id.count as elem_count
group by title
having elem_count > 1