is it possible to group by date when I have the date in an unixtimestamp format? for example in this document: { "position_history": { "1597536000": 109, "1597546800": 118, "1597557600": 123, "1597568400": 124, "1597579200": 124, "1597590000": 124, "1…

SELECT date, AVG(ph.val) AS avg FROM default AS d UNNEST OBJECT_PAIRS(d.position_history) AS ph LET ts = MILLIS_TO_STR(TO_NUMBER(ph.name)*1000), date = DATE_FORMAT_STR(ts,"2020-01-01"); GROUP BY date; SELECT year, AVG(ph.val) AS avg FROM default AS d UNNEST OBJECT_PAIRS(d.positi…

this works, not sure yet how to implement the weeks, but seems that using the DATE_PART_MILLIS was what I need. Also, you may want to correct a typo in the DATE_FROMAT_STR should be DATE_FORMAT_STR

Thanks. [image] Aggregating weekly data in N1QL? In MySQL I would use YEARWEEK() SQL++ How do I generate a weekly aggregates using N1QL? The result I am looking for: count || date 100 || 2020-11-02 300 || 2020-11-09 400 || 2020-11-16 …

DATE_PART_MILLIS is very interesting. I am using it to group my results by week. SELECT sum(TO_NUMBER(o.total_gross_amount)) AS Order Gross Sales, count(1) as Order Count, DATE_PART_MILLIS(received_date_time, “week”) as Week FROM order o WHERE MILLIS_TO_STR(o.received_date_time,“1111-11-11”) b…

[image] Aggregating weekly data in N1QL? In MySQL I would use YEARWEEK() SQL++ How do I generate a weekly aggregates using N1QL? The result I am looking for: count || date 100 || 2020-11-02 300 || 2020-11-09 400 || 2020-11-16 In MySQL …

Group by date (day,week,month,year) from unixtimestamp

SQL++

vsr1 February 10, 2021, 7:57pm 4

Thanks.

Topic		Replies	Views
Aggregating weekly data in N1QL? In MySQL I would use YEARWEEK() SQL++ query , n1ql	2	978	November 21, 2020
Analytics N1QL Query: GROUP BY `dates` base on the given date values Analytics query , n1ql , analytics	4	2483	September 22, 2020
GROUP BY Date - day Mobile query , java , mobile	6	3004	March 20, 2018
How to group result based on time intervals SQL++ n1ql	15	1443	November 6, 2023
GROUP BY performance SQL++ java , n1ql	13	3446	July 13, 2017

Group by date (day,week,month,year) from unixtimestamp

Related topics