[SOLVED] ClickHouse - creating time table

ClickHouse - creating time table

I'm trying to create a time table on the ClickHouse. This is a simple table consisting of 4 fields. The first field shows the date + time in one minute increments ranging from 00:00:00 to 23:59:00. The second field shows the hour in 24 format. The third field shows minutes in the range from 00 to 59. The fourth field indicates the time of day: morning, afternoon, evening.

Here's the code I'm using.

CREATE TABLE time_table AS
SELECT
    toDateTime64('2023-01-01 ' || toHour(number) || ':' || floor(number / 60) || ':' || (number % 60) || ':00', 6) AS tm,
    toHour(tm) AS hour,
    toMinute(tm) AS minute,
    CASE 
        WHEN toHour(tm) BETWEEN 6 AND 11 THEN 'morning'
        WHEN toHour(tm) BETWEEN 12 AND 17 THEN 'afternoon'
        WHEN toHour(tm) BETWEEN 18 AND 23 THEN 'evening'
        ELSE 'night'
    END AS time_of_day
FROM numbers(1440) AS number;

Unfortunately, I'm getting this error and can't figure out how to solve it:

SQL Error [43] [07000]: Code: 43. DB::Exception: Illegal type UInt64 of argument of function toHour. 
Should be Date, Date32, DateTime or DateTime64: 
While processing 
toDateTime64(concat('2023-01-01 ', toHour(number), ':', floor(number / 60), ':', number % 60, ':00'), 6) AS tm, 
toHour(tm) AS hour, 
toMinute(tm) AS minute, 
multiIf((toHour(tm) >= 6) AND (toHour(tm) <= 11), 'утро', 
(toHour(tm) >= 12) AND (toHour(tm) <= 17), 'день', 
(toHour(tm) >= 18) AND (toHour(tm) <= 23), 'вечер', 'ночь') AS time_of_day. 
(ILLEGAL_TYPE_OF_ARGUMENT) (version 23.12.2.59 (official build))

Solution

error is produced by 'toHour(number)' expression - toHour accepts DateTime or DateTime64

you can use next query to generate data you want

SELECT
    makeDateTime(2023,1,1,floor(number/24),number % 60, 6) AS tm,
    toHour(tm) AS hour,
    toMinute(tm) AS minute,
    CASE 
        WHEN toHour(tm) BETWEEN 6 AND 11 THEN 'morning'
        WHEN toHour(tm) BETWEEN 12 AND 17 THEN 'afternoon'
        WHEN toHour(tm) BETWEEN 18 AND 23 THEN 'evening'
        ELSE 'night'
    END AS time_of_day
FROM numbers(1440);