CREATE DATABASE IF NOT EXISTS test_cs;

USE test_cs;

CREATE TABLE `data` ( 
  `id` bigint(20) unsigned NOT NULL, 
  `sampled` timestamp NOT NULL
  -- `sampled` datetime NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test_cs`.`data` (`id`, `sampled`) VALUES ('0', '2020-08-04 00:00:00');
INSERT INTO `test_cs`.`data` (`id`, `sampled`) VALUES ('1', '2020-08-04 02:00:00');
INSERT INTO `test_cs`.`data` (`id`, `sampled`) VALUES ('2', '2020-08-04 04:00:00');
INSERT INTO `test_cs`.`data` (`id`, `sampled`) VALUES ('4', '2020-08-04 06:00:00');

select 'dump table:' as '';
select id, sampled, UNIX_TIMESTAMP(sampled) from data;

select 'filter table by greather than date (FROM_UNIXTIME):' as '';
select id, sampled, UNIX_TIMESTAMP(sampled) from data where sampled >= FROM_UNIXTIME(1596513600);

select 'filter table by greather than date (Date string):' as '';
select id, sampled, UNIX_TIMESTAMP(sampled) from data where sampled >= '2020-08-04 04:00:00';

select 'filter table by greather than date (Date string), but wrapped in CTE (WITH):' as '';
with t1 as (select id, sampled, UNIX_TIMESTAMP(sampled) from data where sampled >= '2020-08-04 04:00:00') select * from t1;

select 'filter table by greather than date (Date string), but wrapped in CTE (WITH) using UNIX_TIMESTAMP:' as '';
with t1 as (select id, sampled, UNIX_TIMESTAMP(sampled) from data where UNIX_TIMESTAMP(sampled) >= UNIX_TIMESTAMP('2020-08-04 04:00:00')) select * from t1;

select 'filter table by greather than date, but wrapped in CTE (WITH):' as '';
with t1 as (select id, sampled, UNIX_TIMESTAMP(sampled) from data where sampled >= FROM_UNIXTIME(1596513600)) select * from t1;

select 'filter table by greather than date, but wrapped in a subquery:' as '';
select * from (select id, sampled, UNIX_TIMESTAMP(sampled) from data where sampled >= FROM_UNIXTIME(1596513600)) as t;

select 'filter table by greather than date, but wrapped in CTE (WITH) using UNIX_TIMESTAMP rather than FROM_UNIXTIME:' as '';
with t1 as (select id, sampled, UNIX_TIMESTAMP(sampled) from data where UNIX_TIMESTAMP(sampled) >= 1596513600) select * from t1;

select 'filter table by greather than date, but wrapped in subquery using UNIX_TIMESTAMP rather than FROM_UNIXTIME:' as '';
select * from (select id, sampled, UNIX_TIMESTAMP(sampled) from data where UNIX_TIMESTAMP(sampled) >= 1596513600) as t;
