MySQl MariaDB group timestamp by points

Question

I have the following:

• data points collected per day with associated date - unix timestamp.

I am trying to achieve the following:

• Filter data points per day. i.e suppose I have 4 data points per day, I am trying to reduce it to n data points per day using GROUP BY.

I am using the following query:

    SELECT *,FLOOR(UNIX_TIMESTAMP(date)/((1440/n)*60))    
    AS timekey 
    FROM  `tbl`
    where date < 'date'  
    GROUP BY timekey)m
    UNION
    (SELECT * ,0 As timekey FROM  `tbl` 
    where date > 'date'  );

This according to me should generate timekey for the data points for any row with date < given_date_in_query.

n determines the number of points to be grouped by.

Then the GROUP BY should ultimately reduce the number of data points with respect to n.

The above query works for a table with more than 20000 rows.

for n=2 the datapoints before the specified date are reduced to 2 points(to 2 rows from 4).

Here is the create script:

CREATE TABLE `tbl_a` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `pid` bigint(11) NOT NULL,
   ---
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   ---
   PRIMARY KEY (`id`),
   UNIQUE KEY `phase_id_2` (`phase_id`,`date`),
   KEY `id` (`id`,`pid`),
   KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The query works perfectly for the above table.

However when I try the same query with another db with comparatively less rows it does not work as expected.

It reduces only the date specified in the query and the rest remain untouched.

.i.e only the row corresponding to the given_date in the query is reduced by the query and the rest of the rows remain untouched.

Test table create script:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
)  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Could this be an index error or a database engine exception?

Steps to recreate the problem:
Create test tbl :

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
)  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert data:

INSERT INTO test (date) VALUES ('2015-11-01 00:00:00');  
INSERT INTO test (date) VALUES ('2015-11-01 06:00:00');  
INSERT INTO test (date) VALUES ('2015-11-01 12:00:00');   
INSERT INTO test (date) VALUES ('2015-11-01 18:00:00');   
INSERT INTO test (date) VALUES ('2016-11-01 00:00:00');  
INSERT INTO test (date) VALUES ('2016-11-01 06:00:00');  
INSERT INTO test (date) VALUES ('2016-11-01 12:00:00');  
INSERT INTO test (date) VALUES ('2016-11-01 18:00:00');   
INSERT INTO test (date) VALUES ('2016-11-02 00:00:00');  
INSERT INTO test (date) VALUES ('2016-11-02 06:00:00');  
INSERT INTO test (date) VALUES ('2016-11-02 12:00:00');   
INSERT INTO test (date) VALUES ('2016-11-02 18:00:00');  
INSERT INTO test (date) VALUES ('2017-11-02 00:00:00');  
INSERT INTO test (date) VALUES ('2017-11-02 06:00:00');  
INSERT INTO test (date) VALUES ('2017-11-02 12:00:00');    
INSERT INTO test (date) VALUES ('2017-11-02 18:00:00');  

Run Query:

 SELECT * FROM
    (SELECT *,FLOOR(UNIX_TIMESTAMP(date)/((1440/2)*60))    
    AS timekey 
    FROM  `test`
    where date < '2017-11-02'  
    GROUP BY timekey) m
    UNION
    (SELECT * ,0 As timekey FROM  `test` 
    where date > '2017-11-02');

The above should:

• reduce all data points prior to 2017-11-02 which should give a result of 10.