Sequelize grouping by hours of a date range

Question

I'm new to sequelize (postgres) and I cannot fin in the documentation how to select the hours of the day (date range), group by them and perform a count.

The query looks like this:

SELECT
    COUNT (*),
    EXTRACT (HOUR FROM paid_at) AS HOUR
FROM
    transactions
WHERE paid_at >= '2015-01-01 00:00:00' AND paid_at <= '2015-01-31 23:59:59'
GROUP BY
    HOUR
ORDER BY hour asc

Could someone send me in the right direction please? I find the sequelize documenation very very basic. Advanced examples are missing.

Answer 1

This is a solution I came up with using Sequelize 3.

var payments_by_hour = await Payment.findAll({
  where: { 
    paid_at: {
      $lte: '2015-01-31 23:00:00',
      $gte: '2015-01-01 00:00:00'
    }
  },
  attributes: [
    [ sequelize.fn('date_trunc', 'hour', sequelize.col('updated_at')), 'hour'],
    [ sequelize.fn('count', '*'), 'count']
  ],
  group: 'hour'
});

Also you can use epoch microsecond values (native to JS and PSQL) for the dates like

$lte: new Date(),
$gte: new Date(new Date() - 24 * 3600 * 1000)