Query by month or by year or by specific day

Questions and Answers
1
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?

1 Like
2

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.

1 Like
3

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?

4

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
5

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ā€,

6

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.

7

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

8

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.

9

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

image
image778Ɨ164 2.66 KB

wanna make it UTC+8

10

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

4 Likes
11

Hi using the lt/lte and gt/gte I am not able to get results for ā€˜date Not Equal toā€™ situation. I tried giving a le the selected and greater than the end of selected date. however the result is always empty array. Any ideas?
Thank you.