Is toRelativeWeekNum Optimized and better?

[Kilian S.]

Hello,

I am developing a Datamodell an i read on your homepage, that your functions for operating with Datetime fields are well-optimized?

I wasn't sure if it would make more sence to add an extra column for the weeknumber of the year or use this function, everytime when I want to retrieve data with some special weeknumber?

Is there any real recogniseable difference?

Best regards
Kilian

[Vitaliy Lyudvichenko]

Do you mean that you need to use toRelativeWeekNum(date) - toRelativeWeekNum(toStartOfYear(date)) often in your queries and it is slow?

среда, 15 февраля 2017 г., 16:31:02 UTC+3 пользователь Kilian S. написал:

[Vitaliy Lyudvichenko]

I made small test:

clickhouse-client --max_threads=1 --time -q "SELECT toDateTime(number) AS date, toRelativeWeekNum(date) FROM system.numbers LIMIT 1000000000 FORMAT Null"

6.348

clickhouse-client --max_threads=1 --time -q "SELECT toDateTime(number) AS date, toRelativeWeekNum(date) - toRelativeWeekNum(toStartOfYear(date)) FROM system.numbers LIMIT 1000000000 FORMAT Null"

12.575

It looks like that native toWeekNumOfTheYear() implementations can be faster enough.

But if we add toWeekNumOfTheYear() function we will must to add many similar functions to{Year,Month,Week,Day,Minute,Second}Of{Year,Month,Week,Day,Minute,Second}() functions to support consistency of function set.

среда, 15 февраля 2017 г., 21:31:46 UTC+3 пользователь Vitaliy Lyudvichenko написал:

[man...@gmail.com]

Functions working with date and time are well optimized and usually fast enough.

But column with DateTime could be more difficult to read than some more light column.
It because DateTime contains time up to seconds and that part could be effectively pseudorandom and will not compress well. The server will spent some time just to read this data from filesystem.

At least, if you will use separate Date column instead of DateTime for calculations with dates, it will be much more efficient to read.
Usually, having Date column is enough and you don't need additional column with week number.

Probably having separate column with just pre-calculated week number will be slightly more efficient, but it is questionable, that you need it.

[Kilian S.]

Hi thank your for your answers.

Well i only thought of a method, like in the java Calendar getWeek of the Year.
But since I will have only a few columns (~10) in my table and i want the select statements to be fast. I think i will use an extra colum for the week of the year.

Best regards

Kilian