How to use postgres ::date with knex.js

Question

I have a column of type timestamp. I need to select all records by given date. In sql it's something like:

select * from "table" where "date"::date = '2015-08-22';

I tried following:

db('table').select().where('date::date', '=', date);

But this throws error

error: select * from "table" where "date::date" = $1 - column "date::date" does not exist

because knex place quotes wrong.

Is there any way to perform such query? Or I should use whereRaw?

Answer 1

For dialect specific functionality like this you often need to use knex.raw. In this case you can the shorthand, whereRaw.

db('table').select().where(knex.raw('??::date = ?', ['date', date]));
db('table').select().whereRaw('??::date = ?', ['date', date]);

Answer 2

::someType is a postgres way of using standard cast(something as sometype). You can try to find this cast in your framework.

Other option is to use date_trunc('day',date) = to_date('2015-08-22', 'YYYY-MM-DD') or date_trunc('day',date) = '2015-08-22'