I am trying to translate following sql query into knex:
select count(*) as finished_on_time from task_history
where date = 20160303
and store_id = 2
and (schedule_start_time at time zone 'Australia/sydney' + interval '1' minute * floor (group_duration) )::time >= (finish_time at time zone 'Australia/sydney')::time
Here is what I have been trying on knex:
db.table('task_history')
.count('*')
.where({date: request.params.storeid, store_id: request.params.storeid })
??????
As you can guess, I am not sure which clause to use to handle sql syntax [at time zone Australia/sydney
].
I have been trying to find any similar soloutions on the internet, but ended up here.
http://knexjs.org/#Builder-whereRaw
db.table('task_history')
.count('*')
.where({date: request.params.storeid, store_id: request.params.storeid })
.whereRaw("(schedule_start_time at time zone 'Australia/sydney' + interval '1' minute * floor (group_duration) )::time >= (finish_time at time zone 'Australia/sydney')::time")