Query by month or by year or by specific day

System Information
  • Strapi Version: 3.5.4
  • Operating System: ubuntu
  • Database: mysql
  • Node Version: 14.x
  • NPM Version: 6.x
  • Yarn Version:

is it possible to query by month or by year or date range in end point?

For that, you should use gt/gte and lt/lte filters.

Getting data only for January 2020
http://localhost:1337/users?created_at_gte=2020-01-01&created_at_lte=2020-01-31

Get data for 2020:

http://localhost:1337/users?created_at_gte=2020-01-01&created_at_lte=2020-12-31

As you already learned from above examples, these are date ranges.

Note: You should use only valid dates! Otherwise, you will get errors. For example, you can’t use 2020-02-31 (31 February) as it doesn’t exist.

To get first date of the month and the last one, you can use momentjs’s startOf('month') and endOf('month') functions.

To get first date of the month and the last one, you can use momentjs’s startOf('month') and endOf('month') functions.

do it from front-end?

Yes, from frontend.

const moment = require('moment'); //use to format dates
const qs = require('qs'); //use to build queries

const firstDayOfTheMonth = moment().startOf('month').format('YYYY-MM-DD'); //2021-04-01
const lastDayOfTheMonth = moment().endOf('month').format('YYYY-MM-DD'); //2021-04-30

const query = qs.stringify({
  created_at_gte: firstDayOfTheMonth,
  created_at_lte: lastDayOfTheMonth 
});

request(`/users?${query}`); // GET /users?created_at_gte=2021-04-01&created_at_lte=2021-04-30

It’s sorta work,but when I request
users?created_at_gte=2020-01-01&created_at_lte=2020-01-31

2020-12-31 is included, any ideas ?
“created_at”: “2020-12-31T16:00:00.000Z”,

Did you mean 2021 or 2020?

If you meant 2021, then you get the result for “2020-12-31T16:00:00.000Z” because your server timezone and database zone differ.

Take a look at the timezone setting:

Also, you should configure the timezone on your server in order to get identical results.

Let’s your timezone from server differs with 12 hours from database, them if you send the request from server with 2020-01-01T00:00:00, then in db it will search 2019-12-31T12:00:00.

thank you for help, Im not sure that have you used AWS RDS its mysql engine, cuz i’ve set the parameter group to desire timezone, and the ubuntu server timezone to the same one

AWS RDS should be UTC unless you modify the setting. You need to convert the datetime from whatever your local TZ is to UTC before you make the query.

I changed the RDS back to UTC, and set the ubuntu local time to UTC


wanna make it UTC+8

I strongly recommend everyone (regardless of skill) read this very nice article about time and programming/development:

With the TLDR being:

Keep everything simple as much as possible. Even if it means being really inefficient, or redundant, or ugly. Everything adds up, and I think usually programmers overthink time. (Leave overthinking for the times where it’s actually critical to overthink.)

Handle more in the client. This is a little bit of an odd piece of advice, but especially when you have a central service (GraphQL, REST API, some central interface, particularly when hooked up with a single page app, mobile clients, or other clients), I think it’s more important to have as simple a central service as possible. This means it should basically only speak UTC, and might not even handle a lot of the more complicated logic around recurring events, timezones, and so on. It adds redundancy in your clients, but I think it makes it easier to understand overall.

Leverage standards and let others do the heavy lifting. Obviously that means using the Brightest Point in Human History , also known as ISO 8601, but it also means using premade time libraries, resources like the Olson database, and leveraging other people’s existing work as much as possible. Let someone else figure out the really gnarly edge cases so you can work on your domain’s edge cases.

To that end, the entire backend be that the backend application, server, database, whatever all should be set in UTC with no timezones everything needs to talk in UTC and you handle the conversion of time at the client side (who knows it’s own timezone).

Never, ever ever, set a server or database timezone outside of UTC

2 Likes