Support now in params for DATETIME fields

[rijkvanzanten]

To make working with dates easier, we should add a now keyword when working with date fields. This will make it easier to do item queries like “past 5 days” or “today”

[wellingguzman]

We should add today along side with now.

now means "right now" which includes time when used on datetime, while today means current day, ignoring the time of the day.

Example:

• Fetching users registered today: GET /_/items/customers?filter[registered_on][eq]=today
• Fetching "published" articles as now: GET /_/items/articles?filter[published_on][eq]=now

The second example will ignore the any article that are scheduled to be published later in the day

[benhaynes]

It seems like this is more of an issue with date vs datetime. Adding today is nice, but it doesn't;'t really solve the problem... we still need an easy way to say:

Is it tomorrow?
Is it yesterday?
Is it September 7th, 2018?

It seems like we need a standardized way to ignore the time... then, instead of today you could say now and ignore_time = true.

Otherwise you need to do a "between" for 00:00:00 and 24:00:00 for the day you're checking.

Thoughts?

[wellingguzman]

Adding today is nice, but it doesn't;'t really solve the problem...

I am not following what you are trying to say. What I am suggesting is that now represents current datetime while today represents current date.

Both will work differently depending if the field is date or datetime.

Going over solution for yesterday, 2 days ago that's another subject, I just wanted to make a quick way for users to fetch the current time based on the server and not the client.

Using complete date is same as it is right now.:

• 2018-10-28 for October 28th, 2018
• 2018-10-28 20:34:18 for October 28th, 2018 at 8:34pm

it seems like we need a standardized way to ignore the time... then, instead of today you could say now and ignore_time = true.

Leaving the time out is a good way to ignore the time, for example 2018-10-28 instead of 2018-10-28 20:34:18. In the case here today = 2018-10-28 and now = 2018-10-28 20:34:18

Where is the ignore_time = true going to be used?

Otherwise you need to do a "between" for 00:00:00 and 24:00:00 for the day you're checking.

In this case the users need to know what's the current day based on the client and not the server. That's one of the problem this is trying to solve, not having to know the date and letting the server get that information.

[benhaynes]

OK, well let's start with this today shortcut!

I like the idea of removing the submitted time to check the date only (don't compare time). Seems cleaner than an extra param.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[benhaynes]

I think the most important part of this ticket is having an option for now. So let's get that added first.

We can discuss what other options we want to add in the future.

[benhaynes]

Awesome!!!

@rijkvanzanten — how do we want to handle this in the App? Also, where should this go in the Docs?

[rijkvanzanten]

Handle in the app how? Ideally, the filter is a little bit smarter when filtering for a date so it can show a range when filtering for between where now is one of the options.

[benhaynes]

When filtering, you should be able to enter a specific date, or use the now keyword... maybe like a "now" checkbox beside the input? Ideally you should be able to create a bookmarked filter using now so that it is always up-to-date (eg: published articles where published_date > now).

[wellingguzman]

I realized this didn't work for between and in filter. I fixed it with this patch (bd51fdb).

I will add a note in the Docs about this.

[pchebro]

Hi,
I am using below query for filters (deep) but have an issue with date condition with $NOW. The requirement is to retrieve items greater than or equal to current date. If items expiration date is greater than today's date then items are returning fine. But equal condition is not working with _gte. Please let me know if I am missing anything.

deep={
"home_page_news": {
"_sort": "-display_date",
"_limit": 5,
"_filter": {
"_and": [
{
"status": {
"_in": [
"published"
]
}
},
{
"_and": [
{
"effective_date": {
"_lte": "$NOW"
}
},
{
"effective_date": {
"_nnull": ""
}
}
]
},
{
"_or": [
{
"expiration_date": {
"_gte": "$NOW"
}
},
{
"expiration_date": {
"_null": ""
}
}
]
}
]
}

[rijkvanzanten]

@pchebro I think you've found yourself in the wrong repo. v8 didn't have logical grouping like that in filters. Try here instead 👍🏻