MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.26


CREATE TABLE testdb ( id int primary key auto_increment,date timestamp);
	INSERT INTO testdb (date) VALUES ('2015-12-31 00:00:00');
	INSERT INTO testdb (date) VALUES ('2015-12-31 06:00:00'); 
	INSERT INTO testdb (date) VALUES ('2015-12-31 18:00:00');
	INSERT INTO testdb (date) VALUES ('2016-01-01 00:00:00');
	INSERT INTO testdb (date) VALUES ('2016-01-01 06:00:00');
	INSERT INTO testdb (date) VALUES ('2016-01-01 18:00:00');
	INSERT INTO testdb (date) VALUES ('2016-01-02 00:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-01-02 06:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-01-02 18:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-12-31 00:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-12-31 06:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-12-31 06:01:00'); 
	INSERT INTO testdb (date) VALUES ('2016-12-31 12:00:00'); 
	INSERT INTO testdb (date) VALUES ('2016-12-31 18:00:00'); 
	INSERT INTO testdb (date) VALUES ('2017-01-01 00:00:00'); 
	INSERT INTO testdb (date) VALUES ('2017-01-01 06:00:00');
	INSERT INTO testdb (date) VALUES ('2017-01-01 18:00:00');
	INSERT INTO testdb (date) VALUES ('2017-01-02 00:00:01'); 
	INSERT INTO testdb (date) VALUES ('2017-01-02 06:00:00'); 
	INSERT INTO testdb (date) VALUES ('2017-01-02 18:00:00'); 
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    SELECT COUNT(*) from testdb;


COUNT(*)


20


SELECT @@sql_mode;


@@sql_mode


STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


SELECT * FROM
   (SELECT h.* FROM testdb h
   WHERE h.date <= '2016-01-02 23:30:00'
   GROUP BY FLOOR(UNIX_TIMESTAMP(h.date)/((1440/2)*60))
   UNION
   SELECT c.* FROM testdb c
   WHERE c.date> '2016-01-02 23:30:00') m;


id
date


1
2015-12-31 00:00:00

3
2015-12-31 18:00:00

4
2016-01-01 00:00:00

6
2016-01-01 18:00:00

7
2016-01-02 00:00:00

9
2016-01-02 18:00:00

10
2016-12-31 00:00:00

11
2016-12-31 06:00:00

12
2016-12-31 06:01:00

13
2016-12-31 12:00:00

14
2016-12-31 18:00:00

15
2017-01-01 00:00:00

16
2017-01-01 06:00:00

17
2017-01-01 18:00:00

18
2017-01-02 00:00:01

19
2017-01-02 06:00:00

20
2017-01-02 18:00:00

id	date
1	2015-12-31 00:00:00
3	2015-12-31 18:00:00
4	2016-01-01 00:00:00
6	2016-01-01 18:00:00
7	2016-01-02 00:00:00
9	2016-01-02 18:00:00
10	2016-12-31 00:00:00
11	2016-12-31 06:00:00
12	2016-12-31 06:01:00
13	2016-12-31 12:00:00
14	2016-12-31 18:00:00
15	2017-01-01 00:00:00
16	2017-01-01 06:00:00
17	2017-01-01 18:00:00
18	2017-01-02 00:00:01
19	2017-01-02 06:00:00
20	2017-01-02 18:00:00