UInt32 VS DateTime

UInt32_VS_DateTime.sql

create table test_d( a Int64, i UInt32, d DateTime) Engine=MergeTree order by a;

insert into test_d 
select number+rand()%50, toUInt32(toDateTime('2020-01-01 00:00:00')+number) i , toDateTime(i) 
from numbers(1e8);

SELECT
    database,
    table,
    column,
    formatReadableSize(sum(column_data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(column_data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_ratio,
    sum(rows) rows_cnt,
    round(usize / rows_cnt, 2) avg_row_size
FROM system.parts_columns
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE 'test_d')
GROUP BY
    database,
    table,
    column
ORDER BY size DESC;

┌─database─┬─table──┬─column─┬─compressed─┬─uncompressed─┬─compr_ratio─┬──rows_cnt─┬─avg_row_size─┐
│ default  │ test_d │ d      │ 381.34 MiB │ 379.95 MiB   │           1 │ 100000000 │         3.98 │
│ default  │ test_d │ i      │ 381.34 MiB │ 379.95 MiB   │           1 │ 100000000 │         3.98 │
│ default  │ test_d │ a      │ 278.55 MiB │ 759.91 MiB   │        2.73 │ 100000000 │         7.97 │
└──────────┴────────┴────────┴────────────┴──────────────┴─────────────┴───────────┴──────────────┘

drop table test_d;
create table test_d( a Int64, i UInt32, d DateTime) Engine=MergeTree order by a;
insert into test_d 
select number+rand()%3, toUInt32(toDateTime('2020-01-01 00:00:00')+intDiv(number,10)) i , toDateTime(i) 
from numbers(1e8);
┌─database─┬─table──┬─column─┬─compressed─┬─uncompressed─┬─compr_ratio─┬──rows_cnt─┬─avg_row_size─┐
│ default  │ test_d │ a      │ 293.62 MiB │ 759.91 MiB   │        2.59 │ 100000000 │         7.97 │
│ default  │ test_d │ d      │ 54.09 MiB  │ 379.95 MiB   │        7.02 │ 100000000 │         3.98 │
│ default  │ test_d │ i      │ 54.09 MiB  │ 379.95 MiB   │        7.02 │ 100000000 │         3.98 │
└──────────┴────────┴────────┴────────────┴──────────────┴─────────────┴───────────┴──────────────┘


set max_threads=1;

select toDate(d) from test_d format Null;
0 rows in set. Elapsed: 0.535 sec. Processed 100.00 million rows, 400.00 MB (186.85 million rows/s., 747.41 MB/s.)


select toDate(toDateTime(i)) from test_d format Null;
0 rows in set. Elapsed: 0.582 sec. Processed 100.00 million rows, 400.00 MB (171.95 million rows/s., 687.80 MB/s.)