How to query for records less than 48 hours in Postgres using knex.js and objection.js?

Question

I'd like to query for all records less than 48 hours of age using a created_at column.

In PostgreSQL you can do something like:

SELECT * from "media" WHERE updated_at >= now() - '48 hour'::INTERVAL;

How do we write this in objection.js/knex.js without going into raw query (or maybe using some raw for part of the equality)?

I have working logic:

const { raw } = require('objection');

return SomeModel.query()
  .andWhere(raw('updated_at >= now() - \'48 HOUR\'::INTERVAL'))
  .orderBy('updated_at')
  .first();

But I would like to avoid using the raw function if possible so something like:

return SomeModel.query()
  .where('updated_at', '>=', 'i have no idea what to put here')
  .orderBy('updated_at')
  .first();

Upon first thought, since updated_at is an new Date().toISOString() I may be able to do something along the lines of < new Date(new Date().getTime()-48*60*60*1000).toISOString()

But I'm not entirely sure how the Postgres comparator is going to handle this.

Chrominance

Answer 1

const { raw } = require('objection');

SomeModel.query()
  .where('updated_at', '>=', raw(`now() - (?*'1 HOUR'::INTERVAL)`, [48]))
  .orderBy('updated_at')
  .first();

Also wrote example in a way that number of hour can be passed as value binding. If value does not have to be changed then using constant now() - '48 HOUR'::INTERVAL is fine too.

Sizzler

Hm. I guess no matter what we'll end up using something with raw(..). – Chrominance

You will need raw every now and then with knex/objection and there is nothing bad in using it. – Cryoscope

Just for furthering my knowledge, it is possible to do ?*'1 MINUTE as well? – Chrominance

@Chrominance yes you can – Cryoscope