GROUP BY Date - day

I’m using v.2.0 of couchbase-lite-android and I save timestamp (Java Long) for date and want to group records by DAY.
How can I write for couchbase the same SQL request like this one for MySQL:
SELECT date,*
FROM Table
GROUP BY DATE_FORMAT(date, “%m-%y-%d”)
ORDER BY YEAR(date) DESC, MONTH(date) DESC, DAY(date) DESC

and return Map<date, <List < Table > >

do you have example code for this?

We haven’t yet implemented the N1QL functions that would let you convert timestamps to calendar date/times. :frowning:

Could you change your schema to store dates in array form, e.g. [24, 12, 2017]? That would make this easy to do.

We support simple groupBy option in CBM 2.0. We do not have a way of returning the aggregated results for each date key as you want. I have made a simplifying assumption here about the way the Date is stored (It is split into its components). We do not have date manipulation functions of the kind you specify

       DataSource ds = DataSource.database(this.db);

        Expression state = Expression.property("contact.address.state");
        Expression count = Function.count(Expression.intValue(1));
        Expression zip = Expression.property("contact.address.zip");
        Expression maxZip = Function.max(zip);
        Expression gender = Expression.property("gender");


        Expression date = Expression.property("date");
        Expression groupByDate = date;
        Expression year = Expression.property("date.year");
        Expression month = Expression.property("date.month");
        Expression day = Expression.property("date.day");

        Ordering orderingYear = Ordering.expression(year).descending();
        Ordering orderingMonth = Ordering.expression(month).descending();
        Ordering orderingDay = Ordering.expression(day).descending();

        Query q1 = QueryBuilder
                .select(SelectResult.property("date"), SelectResult.all())
                .from(ds)
                .groupBy(groupByDate)
                .orderBy(orderingDay,orderingMonth,orderingYear);

Aggregate Functions (array_agg they are referred to in N1QL ) is something we will be considering in a future release (post 2.0) .

can I create DB index from date? and use it for group by

Actually, looking at your code, what you’d need is to store the date as a string in %yyyy-%mm-%dd form. Then you can just group by that property and sort by it in descending order.

I don’t think its a good idea. If I save date like String %yyyy-%mm-%dd I will lose timezone information…
I prefer to save timestamp (Long) in DB and create String index only. If this is possible?

You can store the date in other forms as well if you need to. I’m just saying that storing a property with the date in that form will allow you to do the query you want.