Getting A Month’s Worth of Data With KnexJS

I’m trying to get a month’s worth of data using KnexJS, I only have the year and month in the form 20xx-mm.

Here’s my Knex query ..

export function getMonthlyData(yearAndMonth, startingFromDay, roomId) {
  return db('events')
    .where('roomId', roomId)
    .whereBetween('start', [`${yearAndMonth}-${startingFromDay} 00:00:00+00`, `${yearAndMonth}-31 23:59:59+00`])
    .select();
}

The issue is, I’m starting at a specific date and going through to the 31’st day, some months have 30 days, some 28 and 29. How do I go about to creating a query that helps me achieve this?

I have tried using SQL’s MONTH function along with Knex’s betweenRaw, but unfortunately, I have only the year and month, and the MONTH function expects a datetime.

Answer

This should solve it

export function getMonthlyData(yearAndMonth, startingFromDay, roomId) {
  const startDate = `${yearAndMonth}-${startingFromDay}`;
  return db('events')
    .where('roomId', roomId)
    .whereRaw(`start >= (TIMESTAMP '${startDate}')::DATE`)
    .whereRaw(`start < date_trunc('MONTH', TIMESTAMP '${startDate}' + interval '1 month')::DATE`)
    .select();
}