Skip to content

Instantly share code, notes, and snippets.

@cerivera
Created August 26, 2015 13:07
Show Gist options
  • Save cerivera/23e808bcbfd25fe8e9b1 to your computer and use it in GitHub Desktop.
Save cerivera/23e808bcbfd25fe8e9b1 to your computer and use it in GitHub Desktop.
Group Bys not behaving properly
> select * from steps where time > '2005-01-01 12:00:00' and time < '2005-01-02 12:00:00'
name: steps
-----------
time			user_id		value
2005-01-01T12:15:00Z	1234567890	48
2005-01-01T12:30:00Z	1234567890	9
2005-01-01T12:45:00Z	1234567890	31
2005-01-01T13:00:00Z	1234567890	44
2005-01-01T13:15:00Z	1234567890	26
2005-01-01T13:30:00Z	1234567890	58
2005-01-01T13:45:00Z	1234567890	57
2005-01-01T14:00:00Z	1234567890	9
2005-01-01T14:15:00Z	1234567890	44
2005-01-01T14:30:00Z	1234567890	50
2005-01-01T14:45:00Z	1234567890	48
2005-01-01T15:00:00Z	1234567890	16
2005-01-01T15:15:00Z	1234567890	29
2005-01-01T15:30:00Z	1234567890	33
2005-01-01T15:45:00Z	1234567890	34
2005-01-01T16:00:00Z	1234567890	33
2005-01-01T16:15:00Z	1234567890	42
2005-01-01T16:30:00Z	1234567890	36
2005-01-01T16:45:00Z	1234567890	45
2005-01-01T17:00:00Z	1234567890	23
2005-01-01T17:15:00Z	1234567890	55
2005-01-01T17:30:00Z	1234567890	5
2005-01-01T17:45:00Z	1234567890	16
2005-01-01T18:00:00Z	1234567890	5
2005-01-01T18:15:00Z	1234567890	42
2005-01-01T18:30:00Z	1234567890	3
2005-01-01T18:45:00Z	1234567890	20
2005-01-01T19:00:00Z	1234567890	8
2005-01-01T19:15:00Z	1234567890	60
2005-01-01T19:30:00Z	1234567890	43
2005-01-01T19:45:00Z	1234567890	3
2005-01-01T20:00:00Z	1234567890	56
2005-01-01T20:15:00Z	1234567890	31
2005-01-01T20:30:00Z	1234567890	30
2005-01-01T20:45:00Z	1234567890	59
2005-01-01T21:00:00Z	1234567890	55
2005-01-01T21:15:00Z	1234567890	8
2005-01-01T21:30:00Z	1234567890	13
2005-01-01T21:45:00Z	1234567890	48
2005-01-01T22:00:00Z	1234567890	36
2005-01-01T22:15:00Z	1234567890	8
2005-01-01T22:30:00Z	1234567890	45
2005-01-01T22:45:00Z	1234567890	21
2005-01-01T23:00:00Z	1234567890	57
2005-01-01T23:15:00Z	1234567890	53
2005-01-01T23:30:00Z	1234567890	55
2005-01-01T23:45:00Z	1234567890	13
2005-01-02T00:00:00Z	1234567890	10
2005-01-02T00:15:00Z	1234567890	40
2005-01-02T00:30:00Z	1234567890	59
2005-01-02T00:45:00Z	1234567890	53
2005-01-02T01:00:00Z	1234567890	30
2005-01-02T01:15:00Z	1234567890	52
2005-01-02T01:30:00Z	1234567890	33
2005-01-02T01:45:00Z	1234567890	37
2005-01-02T02:00:00Z	1234567890	19
2005-01-02T02:15:00Z	1234567890	22
2005-01-02T02:30:00Z	1234567890	7
2005-01-02T02:45:00Z	1234567890	48
2005-01-02T03:00:00Z	1234567890	5
2005-01-02T03:15:00Z	1234567890	19
2005-01-02T03:30:00Z	1234567890	52
2005-01-02T03:45:00Z	1234567890	57
2005-01-02T04:00:00Z	1234567890	16
2005-01-02T04:15:00Z	1234567890	56
2005-01-02T04:30:00Z	1234567890	57
2005-01-02T04:45:00Z	1234567890	9
2005-01-02T05:00:00Z	1234567890	17
2005-01-02T05:15:00Z	1234567890	56
2005-01-02T05:30:00Z	1234567890	47
2005-01-02T05:45:00Z	1234567890	48
2005-01-02T06:00:00Z	1234567890	31
2005-01-02T06:15:00Z	1234567890	28
2005-01-02T06:30:00Z	1234567890	41
2005-01-02T06:45:00Z	1234567890	5
2005-01-02T07:00:00Z	1234567890	42
2005-01-02T07:15:00Z	1234567890	45
2005-01-02T07:30:00Z	1234567890	4
2005-01-02T07:45:00Z	1234567890	27
2005-01-02T08:00:00Z	1234567890	40
2005-01-02T08:15:00Z	1234567890	24
2005-01-02T08:30:00Z	1234567890	14
2005-01-02T08:45:00Z	1234567890	20
2005-01-02T09:00:00Z	1234567890	27
2005-01-02T09:15:00Z	1234567890	35
2005-01-02T09:30:00Z	1234567890	36
2005-01-02T09:45:00Z	1234567890	27
2005-01-02T10:00:00Z	1234567890	24
2005-01-02T10:15:00Z	1234567890	59
2005-01-02T10:30:00Z	1234567890	23
2005-01-02T10:45:00Z	1234567890	39
2005-01-02T11:00:00Z	1234567890	32
2005-01-02T11:15:00Z	1234567890	28
2005-01-02T11:30:00Z	1234567890	4
2005-01-02T11:45:00Z	1234567890	12

> select sum(value) from steps where time >= '2005-01-01 12:00:00' and time < '2005-01-02 00:00:00'
name: steps
-----------
time			sum
2005-01-01T12:00:00Z	1569

> select sum(value) from steps where time >= '2005-01-02 00:00:00' and time <= '2005-01-02 12:00:00'
name: steps
-----------
time			sum
2005-01-02T00:00:00Z	1516

> select sum(value) from steps where time >= '2005-01-01 12:00:00' and time <= '2005-01-02 12:00:00'
name: steps
-----------
time			sum
2005-01-01T12:00:00Z	3085

> select sum(value) from steps where time >= '2005-01-01 12:00:00' and time <= '2005-01-02 12:00:00' group by time(1d)
name: steps
-----------
time			sum
2005-01-01T00:00:00Z	1569
2005-01-02T00:00:00Z	3198
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment