How can I query dateTime field by Month and Year only

[sujit-baniya]

Hello,

I'm writing this on issues since I didn't find any other platform to look for my problem. I was trying to query the recordset based on the DateTime field in terms of Month and Year.

For e.g. I needed like

$query = new ParseQuery("ParseClass");
$query->equalsTo("createdAt", "2017-05");
$parseObject = $query->find();

[montymxb]

Hey @itsursujit. Regarding querying via date time unless you know the exact date you want using equalsTo will probably return nothing unless you have entries that exactly match your request, as opposed to a range.

What you can do to get the range is set an upper and lower bound in your query to return all objects created within a given date range.

$query = new ParseQuery("ParseClass");
// lower bound
$query->greaterThan(
    "createdAt", 
    DateTime::createWithFormat("Y-m-d H:i:s", "2017-05-01 00:00:00")
);
// upper bound
$query->lessThan(
    "createdAt", 
    DateTime::createWithFormat("Y-m-d H:i:s", "2017-06-01 00:00:00")
);

// find all objects that lie between 
// the start of the 1st of May
// and start of the 1st of June
// non-inclusive
$objectsWithinDates = $query->find();

If you need the queries to return results from a given set date (inclusive) you can use ParseQuery::greaterThanEqualTo or the like.

[sujit-baniya]

Thank you for the reply. The above query is getting the object list within a month. What if I needed to get the length of objects by list of ranging... just like SQL Queries with GROUP BY

[montymxb]

You can use ParseQuery::ascending or ParseQuery::descending to alter the order in which the results are returned to you.

// sort by ascending date
$query->ascending("createdAt");

// sort by descending date
$query->descending("createdAt");

There's some pretty good documentation on this and more in the query constraints section of the php sdk docs as well.

::EDIT::

@itsursujit Didn't realize that was not an ORDER BY, oops! In your case we don't have an equivalent for GROUP BY that's built in.

You have a couple options however, the first being you can sort through the data yourself and group by month by checking each date.

The second being you could query for each individual month of results one at a time, that way the only results you get back are those within a singular month.

[montymxb]

@itsursujit were you able to figure out your issue?

[sujit-baniya]

Hey Benjamin,

Thank you for your response. I tried it manually. But manual grouping and adding is not an option. In future can we have the functionality in parse?

[dplewis]

@itsursujit I would recommend a beforeSave / afterSave that takes the createdAt and converts it to a string with Month and Year that you can query with equalTo. As far as groupBy you can use Json/Dictionary keys

[montymxb]

As dplewis has pointed out, there's more than one way to do this, you should definitely be able to make this work without having it built in. Essentially, similar to what's mentioned above, you could simply be fetching everything within a range and grouping by Year and Month alone.

// setting up the query beforehand...
$sorted = []
$objs = $query->find();
foreach($objs as $obj) {
    $dateString = $obj->getCreatedAt()->format('m-Y');
    if(!isset($sorted[$dateString])) {
        $sorted[$dateString] = [];
    }

    $sorted[$dateString][] = $obj;
}

// voila! handle your monthly sorted objects as you wish

If you still feel that this will not suffice you should head over to the parse-server repo and open an issue requesting functionality like GROUP BY. New functionality starts server side, and once merged in is integrated into the sdks.

Although I would encourage you to see if you can make this or any of the above solutions (or any other unnamed approaches) work first before you consider that.

[sujitbaniya]

I'm using the same approach i.e. finding and sorting manually using format('Y-m'). But I would love if we could have aggregate functions and GROUP BY to encourage people like us to have more portability.

[montymxb]

@spbaniya that's good to know that there are others who would be interested in this. After taking a brief look in the parse-server repo the closest prior issue I found was #2238, where distinct is desired. It looks like some progress is being made, however if something were to be written up for groupby it would pretty be similar to how that will unfold.

In the meantime...I have a thought. Perhaps until such functionality is available server side we could setup ParseQuery::groupBy. This would take a key to group the results by after they are returned, and not actually pass anything to the server.

When the functionality is implemented server side we could alter the behavior of ParseQuery::groupBy internally to utilize the server side implementation instead of aggregation in the sdk itself. The forward facing facing interface would not change, and the new functionality could be phased in seamlessly 👍 .

As for additional aggregation (besides count, which is already present) we could approach it in a similar fashion as demand requires. @itsursujit this would provide you with a built in mechanism for sorting, but be warned given that createdAt is set to the second you wouldn't be able to group by that. You would have to add an additional date key, where only the month and year are set (zero everything else), and group by that instead.

Again, this is just a thought. What does everyone think here of it?

[flovilmart]

I like the idea of smarter date queries, with YY-mm, YY-mm–dd patterns as well as ‘1d’ ago etc... we’ll be working in that area soon as it’s something we’ll require in our push schedule work.

[flovilmart]

@marvelm, you may wanna follow up here :)

[montymxb]

So just to make sure this doesn't go by the wayside @flovilmart is there an issue or related PR opened for such functionality that we could mention? Or, if this tentative, perhaps we can open up the such an issue just so it's brought up over at the server.

::EDIT::
Noticed that you mentioned in terms of the push schedule work. I took a look but didn't catch any open PRs/issues over at parse server for that (or for date queries). Perhaps I'm missing something, but I'll leave this here (and open) until the work related to this request (or push scheduling) shows up. Once something starts moving we can reference it instead and close this out.

[flovilmart]

There’s no issue open at the moment, we’re in the process internally and we’ll open the relevant issue when we have better identified all the needs :)

[montymxb]

Just to note #4289 over at parse-server is currently pending and proposes to introduce relative time based queries. Assuming this is completed and merged in we'll be adding the relative query functionality to ParseQuery here in the php sdk.

@itsursujit I believe this would be the functionality you were originally looking for 👍 .

[sujitbaniya]

@montymxb Thank you for sticking with this functionality. I'll check and let you know...

[montymxb]

parse-server release 2.6.5 now has relative time queries 👍 !

I have put together a project for a planned 1.4.0 release. This is more or less a list of the desired features to be implemented before we move up to the next major version.

Depending on how people are tracking this (by releases for example) we may want to bump to something minor like 1.3.1 with just the changes for relative time queries, and perhaps one or two other things. That way everyone can get the new features a bit sooner.

I'll most likely make the addition of relative queries some time this weekend. Everyone is also welcome to take a stab at it and put up a PR sometime sooner. On top of that there's also plenty of other potential PRs in the project above ^. If anyone is still looking to get their hacktoberfest shirts 🎃 (and their name in our next release) feel free to start work on any of those features, or open up an issue relating to that feature with any questions you guys have about getting started!

Other than that I'll keep this issue posted about the progress for relative time queries.

[montymxb]

#360 has been added with the ability to make relative time queries. Details on the usage can be found in the PR. I'll be following up with an additional PR to incorporate details into our docs/README on this page, which will be before we run towards a 1.4.0 release.

Although this functionality is merged in it won't be available until the next release. This may wait until 1.4.0 or will be pushed out as a minor 1.3.1 release, haven't decided yet on that.

[montymxb]

@itsursujit @spbaniya we've recently merged in aggregate functionality (#355) for a pending 1.4.0 release. This allows for such behavior as would be expected from GROUP BY. However I haven't quite checked if we have support in place for grouping by date, like 'Y-m'. If not we'll probably have something like that coming up in a bit.

[acinader]

Here's an example of counting new user creation grouped by day using parse-server aggregate:

     $pipeline = [
            'project' => [
                'objectId' => 1,
                'day' => ['$dayOfMonth' => '$_created_at'],
                'month' => ['$month' => '$_created_at'],
                'year' => ['$year' => '$_created_at']
            ],
            'group' => [
                'objectId' => [
                    'year' => '$year',
                    'month' => '$month',
                    'day' => '$day'
                ],
                'count' => ['$sum' => 1],
            ],
            'sort' => [ '_id.year' => -1, '_id.month' => -1, '_id.day' => -1],
            'limit' => 10,
        ];

        $query = new ParseQuery('_User');
        $results = $query->aggregate($pipeline, true);

I'm going to close this.