SP
Size: a a a
SP
SP
SP
R
SP
DC
DC
DC
SC
SELECT
count(*) AS cnt,
toStartOfInterval(time, toIntervalDay(1)) AS period
FROM metrics
WHERE (toDate(time) >= '2021-01-11') AND (toDate(time) <= '2021-01-12')
GROUP BY period
ORDER BY period ASC
UNION ALL
SELECT
0 AS cnt,
arrayJoin(timeSlots(toDateTime(toDate('2021-01-11'), 'UTC'), toUInt32(dateDiff('second', toDateTime(toDate('2021-01-11')), toDateTime(toDate('2021-01-12')))), toUInt32(86400))) AS period
GROUP BY period
ORDER BY period ASC
┌────cnt─┬──────────────period─┐
│ 391722 │ 2021-01-11 00:00:00 │
│ 468620 │ 2021-01-12 00:00:00 │
└────────┴─────────────────────┘
┌─cnt─┬──────────────period─┐
│ 0 │ 2021-01-11 03:00:00 │
│ 0 │ 2021-01-12 03:00:00 │
└─────┴─────────────────────┘
SELECT timeSlots(toDateTime(toDate('2021-01-11'), 'UTC'), toUInt32(dateDiff('second', toDateTime(toDate('2021-01-11')), toDateTime(toDate('2021-01-12')))), toUInt32(86400)) AS slots
┌─slots─────────────────────────────────────────┐
│ ['2021-01-11 00:00:00','2021-01-12 00:00:00'] │
└───────────────────────────────────────────────┘
DC
SELECT
count(*) AS cnt,
toStartOfInterval(time, toIntervalDay(1)) AS period
FROM metrics
WHERE (toDate(time) >= '2021-01-11') AND (toDate(time) <= '2021-01-12')
GROUP BY period
ORDER BY period ASC
UNION ALL
SELECT
0 AS cnt,
arrayJoin(timeSlots(toDateTime(toDate('2021-01-11'), 'UTC'), toUInt32(dateDiff('second', toDateTime(toDate('2021-01-11')), toDateTime(toDate('2021-01-12')))), toUInt32(86400))) AS period
GROUP BY period
ORDER BY period ASC
┌────cnt─┬──────────────period─┐
│ 391722 │ 2021-01-11 00:00:00 │
│ 468620 │ 2021-01-12 00:00:00 │
└────────┴─────────────────────┘
┌─cnt─┬──────────────period─┐
│ 0 │ 2021-01-11 03:00:00 │
│ 0 │ 2021-01-12 03:00:00 │
└─────┴─────────────────────┘
SELECT timeSlots(toDateTime(toDate('2021-01-11'), 'UTC'), toUInt32(dateDiff('second', toDateTime(toDate('2021-01-11')), toDateTime(toDate('2021-01-12')))), toUInt32(86400)) AS slots
┌─slots─────────────────────────────────────────┐
│ ['2021-01-11 00:00:00','2021-01-12 00:00:00'] │
└───────────────────────────────────────────────┘
metrics
TZ MoscowDC
DC
SELECT t
FROM
(
SELECT toDateTime('2016-06-15 23:00:00', 'Asia/Yekaterinburg') AS t
UNION ALL
SELECT toDateTime('2016-06-15 23:00:00', 'UTC') AS t
)
┌───────────────────t─┐
│ 2016-06-15 23:00:00 │
└─────────────────────┘
┌───────────────────t─┐
│ 2016-06-16 04:00:00 │
└─────────────────────┘
SELECT t
FROM
(
SELECT toDateTime('2016-06-15 23:00:00', 'UTC') AS t
UNION ALL
SELECT toDateTime('2016-06-15 23:00:00', 'Asia/Yekaterinburg') AS t
)
┌───────────────────t─┐
│ 2016-06-15 23:00:00 │
└─────────────────────┘
┌───────────────────t─┐
│ 2016-06-15 18:00:00 │
└─────────────────────┘
SC
DC
SELECT
t,
toTypeName(t)
FROM
(
SELECT toDateTime('2016-06-15 23:00:00', 'UTC') AS t
UNION ALL
SELECT toDateTime('2016-06-15 23:00:00', 'Asia/Yekaterinburg') AS t
)
┌───────────────────t─┬─toTypeName(t)───┐
│ 2016-06-15 18:00:00 │ DateTime('UTC') │
└─────────────────────┴─────────────────┘
┌───────────────────t─┬─toTypeName(t)───┐
│ 2016-06-15 23:00:00 │ DateTime('UTC') │
└─────────────────────┴─────────────────┘
DC
SELECT
t,
toTypeName(t)
FROM
(
SELECT toDateTime('2016-06-15 23:00:00', 'Asia/Yekaterinburg') AS t
UNION ALL
SELECT toDateTime('2016-06-15 23:00:00', 'UTC') AS t
)
┌───────────────────t─┬─toTypeName(t)──────────────────┐
│ 2016-06-15 23:00:00 │ DateTime('Asia/Yekaterinburg') │
└─────────────────────┴────────────────────────────────┘
┌───────────────────t─┬─toTypeName(t)──────────────────┐
│ 2016-06-16 04:00:00 │ DateTime('Asia/Yekaterinburg') │
└─────────────────────┴────────────────────────────────┘
SC
SC
SC
PL
SC