knexjs get data by date and time

Question

I'm using knexjs with postgrsql and I'm trying to get data by all the dates and time that great then current date and time. for example I have this data

              {
                "appointment_id": 1,
                "barber_id": 1,
                "user_id": 1,
                "service_id": 1,
                "user_arrive": false,
                "created_at": "2018-08-26T13:38:47.342Z",
                "updated_at": "2018-08-26T13:38:47.342Z",
                "slot": 0,
                "time": "09:00",
                "date": "2018-08-26"
            },
{
                "appointment_id": 1,
                "barber_id": 1,
                "user_id": 1,
                "service_id": 1,
                "user_arrive": false,
                "created_at": "2018-08-26T13:38:47.342Z",
                "updated_at": "2018-08-26T13:38:47.342Z",
                "slot": 2,
                "time": "19:00",
                "date": "2018-08-26"
            },
            {
                "appointment_id": 2,
                "barber_id": 1,
                "user_id": 1,
                "service_id": 1,
                "user_arrive": false,
                "created_at": "2018-08-26T14:19:24.284Z",
                "updated_at": "2018-08-26T14:19:24.284Z",
                "slot": 0,
                "time": "09:00",
                "date": "2018-08-27"
            }

so I want to get only the second and third objects because now is 2018-08-2018 and time is:18:20pm. I store my time and date as string (for me it was easy to work with it)

my query is

 let date =  moment().format('YYYY-MM-DD')
    let time = moment().format('HH:mm')
    console.log('date',time , 'user_id',user_id)
    return knex('appointments')
    .where('user_id',user_id)
    .andWhere('date','>',date)
    .andWhere('time','>',time)

it give me empty array if I send that the right params such as user_id =1 but if I delete the line of time

        .andWhere('time','>',time)

it give me only next day (the third object) and I want the second and third.

Answer 1

Since you are treating dates and times separately, you need to handle the case where the time is less then the desired time and the date is after the desired date.

In your query, on any dates later than the desired date, but with times before the desired time (on those later dates), you will inappropriately exclude those records. Try changing:

.andWhere('date','>',date)
.andWhere('time','>',time)

instead to be:

.andWhere(function() {
    this.where('date','>',date)
    .orWhere(function() {
        this.where('date','=',date)
        .andWhere('time','>',time) })
})

Also, not to your question but always quite useful, I use the following clause to assist debugging my SQL statements in Knex, to ensure it's generating the SQL that I'm expecting.

.on('query', function(data) {
    console.log('debug query:', data);
})

Full example:

let date =  moment().format('YYYY-MM-DD')
let time = moment().format('HH:mm')
console.log('date',time , 'user_id',user_id)
return knex('appointments')
  .where('user_id',user_id)
  .andWhere(function() {
     this.where('date','>',date)
     .orWhere(function() {
        this.where('date','=',date)
        .andWhere('time','>',time) })
  })
  .on('query', function(data) {
     console.log('debug query:', data);
  })

Hope this helps!