[Feat] $__timeGroup support floor rounding

[yangliuyu]

I have installed grafana v5.1.3 commit: 0871432 on a CentOS 7.4.1708 server, using a MySql 7 database as datasource

I want to group some event by hours, which use $__timeGroup(column, '1h', 0) and want event created_at at whether 17:29:00 or 17:31:00 will be grouped in the same 17 time slot, but the result is grouped into 2 hour slot 17 and 18

The problem is cause by cast rounding, for example:

select UNIX_TIMESTAMP('2018-06-23 15:30:00') / 3600; -> return 424927.5000
select cast(cast(UNIX_TIMESTAMP('2018-06-23 15:29:00') / 3600 as signed) * 3600 as signed); -> return 1529737200
select cast(cast(UNIX_TIMESTAMP('2018-06-23 15:30:00') / 3600 as signed) * 3600 as signed); -> return 1529740800

Is there any configuration that I can controll the $__timeGroup(column, '1h', 0) which can be converted to UNIX_TIMESTAMP(column) div 3600 * 3600

[svenklemm]

$__timeGroup does not do any rounding and always uses floor. But the base for the floor calculation is UTC so if your timezone offset is half an hour it might look like rounding.

The values you posted are 7:00 and 8:00 in UTC so they land in 2 buckets if you group by 1 hour.

postgres=# select to_timestamp(1529737200), to_timestamp(1529740800);
      to_timestamp      |      to_timestamp
------------------------+------------------------
 2018-06-23 07:00:00+00 | 2018-06-23 08:00:00+00
(1 row)

It might be beneficial if timeGroup would allow passing an offset especially if you group by day you might not want timeGroup to group by UTC day but by your local day

[yangliuyu]

@svenklemm thanks, my timezone is UTC+8, no half hour problem

I think the problem is caused by casting as integer
cast(UNIX_TIMESTAMP('2018-06-23 15:29:00') / 3600 as signed
or in another way:
select cast(1.5 as signed) will return 2
so if I floor timestamp to hours, when minute exceed 30, it will be grouped into the next hour slot.

if $__timeGroup(column, '1h', 0) can be converted to UNIX_TIMESTAMP(column) div 3600 * 3600, that will be very helpful.

The $__timeGroup macro's fill default value feature help me draw 0 value in the graph, otherwise I will use
select UNIX_TIMESTAMP(column) div 3600 * 3600 as time_sec directly

[svenklemm]

Hmm you are actually right and there might be rounding involved i have to investigate this further. It should always use floor though and on postgres it does as far as i'm aware but it might not on other databases

postgres=# select (3/2)::int;
 int4
------
    1
(1 row)
postgres=# select 1.5::int;
 int4
------
    2
(1 row)

MariaDB [mysql]> select cast(1.5 as signed);
+---------------------+
| cast(1.5 as signed) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> select cast(3/2 as signed);
+---------------------+
| cast(3/2 as signed) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

[svenklemm]

@yangliuyu OK i double checked on postgres and its not correct there aswell, i think the current behaviour is a bug.