Skip to content

Commit 31b8247

Browse files
orianorian
authored
chore: use proper DateTime parsing function with test (PostHog#29126)
1 parent 0b273d5 commit 31b8247

29 files changed

+1409
-1401
lines changed

‎ee/clickhouse/views/test/__snapshots__/test_clickhouse_stickiness.ambr‎

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
FROM
1111
(SELECT 0 AS num_actors,
1212
plus(numbers.number, 1) AS num_intervals
13-
FROM numbers(ceil(divide(dateDiff('week', toStartOfWeek(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')), 0), plus(toStartOfWeek(assumeNotNull(toDateTime64('2020-02-15 23:59:59', 6, 'UTC')), 0), toIntervalWeek(1))), 1))) AS numbers
13+
FROM numbers(ceil(divide(dateDiff('week', toStartOfWeek(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')), 0), plus(toStartOfWeek(assumeNotNull(toDateTime('2020-02-15 23:59:59', 'UTC')), 0), toIntervalWeek(1))), 1))) AS numbers
1414
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
1515
num_intervals AS num_intervals
1616
FROM
@@ -20,7 +20,7 @@
2020
(SELECT e.`$group_0` AS aggregation_target,
2121
toStartOfWeek(toTimeZone(e.timestamp, 'UTC'), 0) AS start_of_interval
2222
FROM events AS e SAMPLE 1
23-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfWeek(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')), 0)), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-02-15 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'), ifNull(notEquals(nullIf(nullIf(e.`$group_0`, ''), 'null'), ''), 1), notEquals(e.`$group_0`, ''))
23+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfWeek(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')), 0)), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-02-15 23:59:59', 'UTC'))), equals(e.event, 'watched movie'), ifNull(notEquals(nullIf(nullIf(e.`$group_0`, ''), 'null'), ''), 1), notEquals(e.`$group_0`, ''))
2424
GROUP BY aggregation_target,
2525
start_of_interval
2626
HAVING ifNull(greater(count(), 0), 0))
@@ -151,7 +151,7 @@
151151
FROM
152152
(SELECT 0 AS num_actors,
153153
plus(numbers.number, 1) AS num_intervals
154-
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), toIntervalDay(1))), 1))) AS numbers
154+
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), toIntervalDay(1))), 1))) AS numbers
155155
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
156156
num_intervals AS num_intervals
157157
FROM
@@ -168,7 +168,7 @@
168168
WHERE equals(person_distinct_id_overrides.team_id, 99999)
169169
GROUP BY person_distinct_id_overrides.distinct_id
170170
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
171-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'))
171+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), equals(e.event, 'watched movie'))
172172
GROUP BY aggregation_target,
173173
start_of_interval
174174
HAVING ifNull(greater(count(), 0), 0))
@@ -197,7 +197,7 @@
197197
FROM
198198
(SELECT 0 AS num_actors,
199199
plus(numbers.number, 1) AS num_intervals
200-
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime64('2019-12-24 00:00:00', 6, 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime64('2019-12-31 23:59:59', 6, 'UTC'))), toIntervalDay(1))), 1))) AS numbers
200+
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime('2019-12-24 00:00:00', 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime('2019-12-31 23:59:59', 'UTC'))), toIntervalDay(1))), 1))) AS numbers
201201
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
202202
num_intervals AS num_intervals
203203
FROM
@@ -214,7 +214,7 @@
214214
WHERE equals(person_distinct_id_overrides.team_id, 99999)
215215
GROUP BY person_distinct_id_overrides.distinct_id
216216
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
217-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime64('2019-12-24 00:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2019-12-31 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'))
217+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime('2019-12-24 00:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2019-12-31 23:59:59', 'UTC'))), equals(e.event, 'watched movie'))
218218
GROUP BY aggregation_target,
219219
start_of_interval
220220
HAVING ifNull(greater(count(), 0), 0))
@@ -243,7 +243,7 @@
243243
FROM
244244
(SELECT 0 AS num_actors,
245245
plus(numbers.number, 1) AS num_intervals
246-
FROM numbers(ceil(divide(dateDiff('week', toStartOfWeek(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')), 0), plus(toStartOfWeek(assumeNotNull(toDateTime64('2020-02-15 23:59:59', 6, 'UTC')), 0), toIntervalWeek(1))), 1))) AS numbers
246+
FROM numbers(ceil(divide(dateDiff('week', toStartOfWeek(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')), 0), plus(toStartOfWeek(assumeNotNull(toDateTime('2020-02-15 23:59:59', 'UTC')), 0), toIntervalWeek(1))), 1))) AS numbers
247247
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
248248
num_intervals AS num_intervals
249249
FROM
@@ -268,7 +268,7 @@
268268
WHERE and(equals(groups.team_id, 99999), equals(index, 0))
269269
GROUP BY groups.group_type_index,
270270
groups.group_key) AS e__group_0 ON equals(e.`$group_0`, e__group_0.key)
271-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfWeek(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')), 0)), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-02-15 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'), ifNull(equals(e__group_0.properties___industry, 'technology'), 0))
271+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfWeek(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')), 0)), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-02-15 23:59:59', 'UTC'))), equals(e.event, 'watched movie'), ifNull(equals(e__group_0.properties___industry, 'technology'), 0))
272272
GROUP BY aggregation_target,
273273
start_of_interval
274274
HAVING ifNull(greater(count(), 0), 0))
@@ -428,7 +428,7 @@
428428
FROM
429429
(SELECT 0 AS num_actors,
430430
plus(numbers.number, 1) AS num_intervals
431-
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), toIntervalDay(1))), 1))) AS numbers
431+
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), toIntervalDay(1))), 1))) AS numbers
432432
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
433433
num_intervals AS num_intervals
434434
FROM
@@ -445,7 +445,7 @@
445445
WHERE equals(person_distinct_id_overrides.team_id, 99999)
446446
GROUP BY person_distinct_id_overrides.distinct_id
447447
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
448-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'))
448+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), equals(e.event, 'watched movie'))
449449
GROUP BY aggregation_target,
450450
start_of_interval
451451
HAVING ifNull(greater(count(), 0), 0))
@@ -496,7 +496,7 @@
496496
FROM
497497
(SELECT 0 AS num_actors,
498498
plus(numbers.number, 1) AS num_intervals
499-
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), toIntervalDay(1))), 1))) AS numbers
499+
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), toIntervalDay(1))), 1))) AS numbers
500500
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
501501
num_intervals AS num_intervals
502502
FROM
@@ -513,7 +513,7 @@
513513
WHERE equals(person_distinct_id_overrides.team_id, 99999)
514514
GROUP BY person_distinct_id_overrides.distinct_id
515515
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
516-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'))
516+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), equals(e.event, 'watched movie'))
517517
GROUP BY aggregation_target,
518518
start_of_interval
519519
HAVING ifNull(greater(count(), 0), 0))
@@ -553,7 +553,7 @@
553553
FROM
554554
(SELECT 0 AS num_actors,
555555
plus(numbers.number, 1) AS num_intervals
556-
FROM numbers(ceil(divide(dateDiff('hour', toStartOfHour(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC'))), plus(toStartOfHour(assumeNotNull(toDateTime64('2020-01-01 20:00:00', 6, 'UTC'))), toIntervalHour(1))), 1))) AS numbers
556+
FROM numbers(ceil(divide(dateDiff('hour', toStartOfHour(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC'))), plus(toStartOfHour(assumeNotNull(toDateTime('2020-01-01 20:00:00', 'UTC'))), toIntervalHour(1))), 1))) AS numbers
557557
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
558558
num_intervals AS num_intervals
559559
FROM
@@ -570,7 +570,7 @@
570570
WHERE equals(person_distinct_id_overrides.team_id, 99999)
571571
GROUP BY person_distinct_id_overrides.distinct_id
572572
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
573-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfHour(assumeNotNull(toDateTime64('2020-01-01 12:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-01-01 20:00:00', 6, 'UTC'))), equals(e.event, 'watched movie'))
573+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfHour(assumeNotNull(toDateTime('2020-01-01 12:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-01-01 20:00:00', 'UTC'))), equals(e.event, 'watched movie'))
574574
GROUP BY aggregation_target,
575575
start_of_interval
576576
HAVING ifNull(greater(count(), 0), 0))
@@ -704,7 +704,7 @@
704704
FROM
705705
(SELECT 0 AS num_actors,
706706
plus(numbers.number, 1) AS num_intervals
707-
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), toIntervalDay(1))), 1))) AS numbers
707+
FROM numbers(ceil(divide(dateDiff('day', toStartOfDay(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC'))), plus(toStartOfDay(assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), toIntervalDay(1))), 1))) AS numbers
708708
UNION ALL SELECT count(DISTINCT aggregation_target) AS num_actors,
709709
num_intervals AS num_intervals
710710
FROM
@@ -721,7 +721,7 @@
721721
WHERE equals(person_distinct_id_overrides.team_id, 99999)
722722
GROUP BY person_distinct_id_overrides.distinct_id
723723
HAVING ifNull(equals(argMax(person_distinct_id_overrides.is_deleted, person_distinct_id_overrides.version), 0), 0) SETTINGS optimize_aggregation_in_order=1) AS e__override ON equals(e.distinct_id, e__override.distinct_id)
724-
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime64('2020-01-01 00:00:00', 6, 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime64('2020-01-08 23:59:59', 6, 'UTC'))), equals(e.event, 'watched movie'))
724+
WHERE and(equals(e.team_id, 99999), greaterOrEquals(toTimeZone(e.timestamp, 'UTC'), toStartOfDay(assumeNotNull(toDateTime('2020-01-01 00:00:00', 'UTC')))), lessOrEquals(toTimeZone(e.timestamp, 'UTC'), assumeNotNull(toDateTime('2020-01-08 23:59:59', 'UTC'))), equals(e.event, 'watched movie'))
725725
GROUP BY aggregation_target,
726726
start_of_interval
727727
HAVING ifNull(greater(count(), 0), 0))

0 commit comments

Comments
 (0)