Skip to content

Instantly share code, notes, and snippets.

@akurkin

akurkin/rounding_time.txt

Forked from alexey-milovidov/rounding_time.txt
Created June 21, 2016 17:35
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save akurkin/dfbd85e3f0fe3854168b5d0886bc84c3 to your computer and use it in GitHub Desktop.
Save akurkin/dfbd85e3f0fe3854168b5d0886bc84c3 to your computer and use it in GitHub Desktop.
Download ZIP
Example of rounding time to 5-second interval in ClickHouse.
Raw
rounding_time.txt
:) SELECT toDateTime(intDiv(toUInt32(EventTime), 5) * 5) AS k, count(), uniq(UserID) FROM hits_layer WHERE CounterID = 29761725 AND EventDate = today() AND EventTime >= now() - 600 GROUP BY k ORDER BY k
SELECT
toDateTime(intDiv(toUInt32(EventTime), 5) * 5) AS k,
count(),
uniq(UserID)
FROM hits_layer
WHERE (CounterID = 29761725) AND (EventDate = today()) AND (EventTime >= (now() - 600))
GROUP BY k
ORDER BY k ASC
┌───────────────────k─┬─count()─┬─uniq(UserID)─┐
│ 2016-06-20 23:49:55 │ 1 │ 1 │
│ 2016-06-20 23:50:00 │ 1 │ 1 │
│ 2016-06-20 23:50:05 │ 1 │ 1 │
│ 2016-06-20 23:50:10 │ 1 │ 1 │
│ 2016-06-20 23:50:25 │ 3 │ 1 │
│ 2016-06-20 23:50:45 │ 1 │ 1 │
│ 2016-06-20 23:50:50 │ 1 │ 1 │
│ 2016-06-20 23:51:00 │ 1 │ 1 │
│ 2016-06-20 23:51:05 │ 2 │ 1 │
│ 2016-06-20 23:51:25 │ 1 │ 1 │
│ 2016-06-20 23:51:30 │ 1 │ 1 │
│ 2016-06-20 23:51:40 │ 1 │ 1 │
│ 2016-06-20 23:51:55 │ 1 │ 1 │
│ 2016-06-20 23:52:05 │ 2 │ 2 │
│ 2016-06-20 23:52:15 │ 1 │ 1 │
│ 2016-06-20 23:52:30 │ 1 │ 1 │
│ 2016-06-20 23:53:20 │ 1 │ 1 │
│ 2016-06-20 23:53:35 │ 1 │ 1 │
│ 2016-06-20 23:54:05 │ 1 │ 1 │
│ 2016-06-20 23:54:20 │ 1 │ 1 │
│ 2016-06-20 23:56:00 │ 1 │ 1 │
│ 2016-06-20 23:56:05 │ 3 │ 1 │
│ 2016-06-20 23:56:45 │ 2 │ 2 │
│ 2016-06-20 23:57:00 │ 1 │ 1 │
│ 2016-06-20 23:57:15 │ 1 │ 1 │
│ 2016-06-20 23:57:20 │ 1 │ 1 │
│ 2016-06-20 23:57:25 │ 1 │ 1 │
└─────────────────────┴─────────┴──────────────┘
27 rows in set. Elapsed: 0.053 sec. Processed 352.26 thousand rows, 6.34 MB (6.70 million rows/s., 120.55 MB/s.)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment