knex: select rows that are in certain date range

Question

How can I select rows from the table that are in certain date range with knex queries? For example, selecting rows from last seven days.

Knex version: 0.15.0

DB: PostgreSQL

somecases `whereRaw` is your friend. – Fazal Rasel Jul 26 '18 at 19:36 — Fazal Rasel, Jul 26 '18 at 19:36
Yeap, seems that I will have to use whereRaw – Ziyaddin Sadigov Jul 26 '18 at 19:56 — Ziyaddin Sadigov, Jul 26 '18 at 19:56
this is assuming `createdAt` is `timestamp` type, correct? – Ziv Levy Apr 24 '22 at 13:25 — Ziv Levy, Apr 24 '22 at 13:25

score 47 · Accepted Answer · answered Aug 03 '18 at 08:17

47

Any way you choose to query them should do, here is one way to do it:

knex('table')
  .where('createdAt', '>=', '2009-01-01T00:00:00Z')
  .where('createdAt', '<', '2010-01-01T00:00:00Z')

answered Aug 03 '18 at 08:17

Mikael Lepistö

18,909
3
68
70

It works for a specific timestamp. I always want to select the rows of last seven days beginning from the current timestamp. Maybe I can use `moment` to get the current timestamp and compare with that one? – Ziyaddin Sadigov Aug 03 '18 at 12:10
That would be one way to do it. – Mikael Lepistö Aug 04 '18 at 15:52

score 27 · Answer 2 · edited Jan 30 '23 at 16:52

27

You can also use:

const from = '2019-01-01';
const to = '2019-02-02';

knex('myTable')
  .select()
  .whereBetween('createdAt', [from, to]);

knex.js whereBetween docs.

edited Jan 30 '23 at 16:52

double-beep

5,031
17
33
41

answered Sep 02 '19 at 07:52

kaiak

1,759
1
13
11

score 2 · Answer 3 · answered Sep 15 '19 at 15:33

If you are using moment, it will be pretty easy to achieve what you want. create an ISO 8601 format date from the date string you have.

let startDate = '2019-01-01';
startDate = moment(startDate).format('YYYY-MM-DDTHH:mm:ssZ');
let endtDate = '2019-10-01';
endDate = moment(endDate).format('YYYY-MM-DDTHH:mm:ssZ');

toISOString() method will format the date in YYYY-MM-DD[T]HH:mm:ss.SSS[Z] format, which can be used with knex to find the record in time range. However, I prefer the format() method over toISOString() because format method uses the default format (YYYY-MM-DDTHH:mm:ssZ) without milliseconds and maintains the timezone offset.

Now, you can find the relevant records in the given range of startDate and endDate as given below,

knex('records')
  .where('created_at', '>=', startDate.toString())
  .where('created_at', '<', endDate.toString())
  .then((rows) => {
     /*
      * perform operations on record
      */
  })
  .catch((e) => {
       console.log(e);
  });

or alternatively, you can use whereBetween() from knex as,

knex('records')
  .whereBetween('created_at', [startDate.toString() , endDate.toString()])
  .then((rows) => {
     /*
      * perform operations on record
      */
  })
  .catch((e) => {
       console.log(e);
  });

Note: The createdAt column name is replaced with created_at in the most recent versions of knex. At the time writing this answer i used knex@0.19.1. But still you can use createdAt with older versions of knex.

this is assuming `createdAt` is `timestamp` type, correct? – Ziv Levy Apr 24 '22 at 13:25 — Ziv Levy, Apr 24 '22 at 13:25

Morgan Huegel · Answer 4 · 2019-09-06T18:13:14.640

You can just create two different timestamps using Javascript Date() object, and convert them to strings.

    const currentDate = new Date()

    // Must convert to strings in case you need to prefix a '0' for single digit months or dates
    const currentYear = "" + currentDate.getUTCFullYear()
    const currentMonth = "" + (currentDate.getUTCMonth() + 1) //month indexed at 0
    const currentDay = "" + currentDate.getUTCDate()
    const currentHour = "" + currentDate.getUTCHours()
    const currentMinute = "" + currentDate.getUTCMinutes()
    const currentSeconds = "" + currentDate.getUTCSeconds()

    //Timestamp format: YYYY-MM-DDTHH:MM:SSZ
    const yesterdayTimestamp = `${currentYear}-` +
      // Ternaries are for prefixing a 0 to return values that are single digit
      `${currentMonth.length === 2 ? currentMonth : '0' + currentMonth}-` +
      `${currentDay.length === 2 ? currentDay : '0' + currentDay}T` +
      `${currentHour.length === 2 ? currentHour : '0' + currentHour}:` +
      `${currentMinute.length === 2 ? currentMinute : '0' + currentMinute}:` +
      `${currentSeconds.length === 2 ? currentSeconds : '0' + currentSeconds}Z`

    const lastWeek = new Date()
    lastWeek.setUTCDate(currentDate.getUTCDate() - 1)
    // Then do same string manipulations to create timestamp string for one week ago...

score 1 · Answer 5 · answered Apr 16 '20 at 06:16

In your Repo, just put these where clause:

  MODEL.query()
          .where('created_at', '>=', '2009-01-01T00:00:00Z')
          .where('created_at', '<', new Date())
          .orderBy('created_at','desc|asc')

Above code is useful to get all records from 2009-01-01 till the ongoing date(You can replace new Date() with your own date till which you want the records).

Ques : How to get 2009-01-01T00:00:00Z from 04/14/2020(MM/DD/YYYY)?

Answer :

dateSplit = date.split("/");
            date = `${dateSplit[2]}-${dateSplit[0]}-${dateSplit[1]}`;
            date = date + "T00:00:00.000Z";

Note: Do not forget to send date in MM/DD/YYYY. Also it solves the problem of date or time being shown as incremented or decremented.

knex: select rows that are in certain date range

5 Answers5