Example of using date and datetime functions in ClickHouse.

rounding_dates.txt

:) SELECT toMonday(EventDate) AS k, count(), uniq(UserID) FROM hits_layer WHERE CounterID = 29761725 AND EventDate >= '2016-05-01' GROUP BY k ORDER BY k

SELECT 
    toMonday(EventDate) AS k, 
    count(), 
    uniq(UserID)
FROM hits_layer
WHERE (CounterID = 29761725) AND (EventDate >= '2016-05-01')
GROUP BY k
ORDER BY k ASC

┌──────────k─┬─count()─┬─uniq(UserID)─┐
│ 2016-04-25 │    3925 │         1172 │
│ 2016-05-02 │   61063 │        13939 │
│ 2016-05-09 │   76369 │        16602 │
│ 2016-05-16 │   88299 │        18256 │
│ 2016-05-23 │   84800 │        17172 │
│ 2016-05-30 │   85837 │        17256 │
│ 2016-06-06 │   82312 │        16246 │
│ 2016-06-13 │   77071 │        15184 │
│ 2016-06-20 │   13780 │         3386 │
└────────────┴─────────┴──────────────┘

9 rows in set. Elapsed: 0.104 sec. Processed 1.03 million rows, 14.45 MB (9.89 million rows/s., 138.41 MB/s.)