CODEWITHSUNDEEP
node.jspostgresqldateknex.js
Boris Zagoruiko
Boris Zagoruiko

Reputation: 13174

How to use postgres ::date with knex.js

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?

Upvotes: 7

Views: 11526

Answers (3)

Rhys van der Waerden
Rhys van der Waerden

Reputation: 3857

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]);

Upvotes: 15

vitaly-t
vitaly-t

Reputation: 25900

You got the type casting idea reversed, hence the issue: Data casting is to be applied to values, not to column names.

i.e. change to this:

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

And you can still use a date/time function on column date, if you need to extract a part from it, or to match to another type.

Upvotes: -1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28571

::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'

Upvotes: 3

Related Questions