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

SQL++
1

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,
      "1597600800": 130,
      "1597611600": 132,
      "1597622400": 134,
      "1597633200": 137,
      "1597644000": 141,
      "1597654800": 144,
      "1597665600": 145,
      "1597676400": 146,
      "1597759200": 159,
      "1597762800": 159,
      "1597766400": 162,
      "1597768200": 162,
      "1597770000": 162,
      "1597771800": 162,
      "1597773600": 162,
      "1597775400": 170,
      "1597777200": 170,
      "1597779000": 170,
      "1597780800": 170,
      "1597782600": 170,
      "1597784400": 170,
      "1597786200": 170,
      "1597788000": 170,
      "1597789800": 170,
      "1597791600": 170,
      "1597793400": 170,
      "1597795200": 170,
      "1597797000": 172,
      "1597800600": 172,
      "1597802400": 172,
      "1597804200": 172,
      "1597806000": 172,
      "1597807800": 173,
      "1597809600": 173,
      "1597811400": 173,
      "1597813200": 173,
      "1597815000": 173,
      "1597816800": 173,
      "1597818600": 173,
      "1597820400": 164,
      "1597822200": 164,
      "1597824000": 164,
      "1597825800": 164,
      "1597827600": 164,
      "1597833000": 162,
      "1597834800": 162,
      "1597836600": 162,
      "1597838400": 162,
      "1597840200": 144,
      "1597842000": 144,
      "1597843800": 144,
      "1597845600": 144,
      "1597847400": 144,
      "1597849200": 144,
      "1597851000": 142,
      "1597852800": 142,
      "1597854600": 142,
      "1597856400": 142,
      "1597858200": 142,
      "1597860000": 142,
      "1597861800": 147,
      "1597863600": 147,
      "1597883400": 153,
      "1597885200": 153,
      "1597887000": 153,
      "1597888800": 153,
      "1597890600": 153,
      "1597892400": 153,
      "1597894200": 167,
      "1597896000": 167,
      "1597897800": 167,
      "1597899600": 167,
      "1597901400": 167,
      "1597903200": 167,
      "1597905000": 170,
      "1597906800": 170,
      "1597908600": 170,
      "1597910400": 170,
      "1597912200": 170,
      "1597914000": 170,
      "1597917600": 172,
      "1597919400": 172,
      "1597921200": 172,
      "1597923000": 172,
      "1597924800": 172,
      "1597926600": 172,
      "1597928400": 171,
      "1597930200": 171,
      "1597932000": 171,
      "1597933800": 171,
      "1597935600": 171,
      "1597937400": 171,
      "1597941000": 177,
      "1597942800": 177,
      "1597944600": 177
    }
  }

the field/key is the unix timestamp and the value is a position in a top.

Would it be possible to group by day/week/year (based on the timestamps) and do the average on the value?

in mysql you could do something like:

SELECT
AVG(something) AS 12
FROM
table
WHERE
something
GROUP BY
DAY/WEEK/YEAR(
FROM_UNIXTIME(date, ā€˜%Y-%m-%d %H.%i.%sā€™)
)

2 
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.position_history) AS ph
    LET ms = TO_NUMBER(ph.name)*1000,
             year = DATE_PART_MILLIS(ms,"year"),
     GROUP BY year;

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html#fn-date-part-millis

Use date functions from here. Note your time is in seconds, date functions need MILLIS
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/datefun.html

Week is little odd because it doesnā€™t start same every year. check week, iso_week, which returns 53

1 Like
3

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

4

Thanks.

5

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ā€) between ā€œ2021-01-01ā€ and ā€œ2021-03-08ā€
AND xyzā€¦
group by DATE_PART_MILLIS(received_date_time, ā€œweekā€)
order by DATE_PART_MILLIS(received_date_time, ā€œweekā€);

Now, this gives back results in the following format with week numbers representing weeks.

{ ā€œOrder Countā€: 4111,
ā€œOrder Gross Salesā€: 56662.95999999997,
ā€œWeekā€: 1 }, {
ā€œOrder Countā€: 5676,
ā€œOrder Gross Salesā€: 78037.26999999993,
ā€œWeekā€: 2 }, {
ā€œOrder Countā€: 8275,
ā€œOrder Gross Salesā€: 129023.439999999,
ā€œWeekā€: 3 } ā€¦

Is there a way to get a ā€˜Week Startingā€™ date instead of week number? Iā€™d rather get ā€˜2021-01-03ā€™ instead of week ā€˜1ā€™?

6

has solution

SELECT SUM(TO_NUMBER(o.total_gross_amount)) AS OrderGrossSales, COUNT(1) AS OrderCount, wdt AS week
FROM order AS o
LET dt = MILLIS_TO_STR(o.received_date_time,"1111-11-11"),
    wdt = DATE_ADD_STR(dt, {"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(dt)],"day")
WHERE dt BETWEEN "2021-01-01" AND "2021-03-08"
GROUP BY wdt
ORDER BY wdt;

test start of week

SELECT wdt
LET dt = MILLIS_TO_STR(MILLIS("2021-01-05"),"1111-11-11"),
    wdt = DATE_ADD_STR(dt, {"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(dt)],"day");
7

Awesome, thanks much