Skip to content

Instantly share code, notes, and snippets.

@akurkin
Forked from alexey-milovidov/rounding_dates.txt
Created June 21, 2016 17:36
Show Gist options
  • Save akurkin/bfe7aac7a71dbf246466986d5a5c2734 to your computer and use it in GitHub Desktop.
Save akurkin/bfe7aac7a71dbf246466986d5a5c2734 to your computer and use it in GitHub Desktop.
Example of using date and datetime functions in ClickHouse.
:) 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.)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment