How to handle timestamp with timezone on postgres with knex.js

Question

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

date field has in yyyymmdd format

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.

morpher · Accepted Answer · 2016-05-25 07:57:32Z

3

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")

answered May 25, 2016 at 7:57

morpher

461 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to handle timestamp with timezone on postgres with knex.js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related