Knex, how to select rows that are within a certain date range-CodePudding

I want to select all rows using Knex which have been created in the past 30 days but I can't figure out how to do that. I know that I could for example select the rows manually using this code here:

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

However, I'd like the selection to update automatically so that I don't have to update the code every month.

Meaning, that it should now automatically select the rows from the 7th of November till the 7th of December, next month it should select the rows from the 7th of December till 7th of January, and so on.

I'm using MySQL but I don't know that should make a difference as I'm using Knex for the queries anyway.

CodePudding user response：

To select all rows using Knex that have been created in the past 30 days, you could use the following query:

knex.select('*')
    .from('table_name')
    .where(knex.raw('DATEDIFF(CURDATE(), created_at) <= 30'))

This query uses the DATEDIFF function to calculate the number of days between the current date and the created_at field.

To get recurrent results for the future, set up a CRON job for the query and logging, e.g., using crontab in UNIX.