Using INTERVAL for querying date range

user_2676466 0
Viewed 438

We use clickhouse as our database and there is a table with column of type DateTime(UTC).

I needed to query date range, for example something like this:

SELECT * 
FROM some_table 
WHERE date_time_column BETWEEN ? AND ?

I have parameters Start and End which are of type time.Time but client had asked for additional option:

Basically, they want to provide Start and an offset. Offset may be day, hour, week or month.

Since I am new hire, and have never worked with clickhouse before, I came here for help.

MY EFFORTS TO SOLVE THE PROBLEM:

I have investigated clickhouse documentation and found INTERVAL.

It looks great for my task, because I had something like this in mind:

SELECT * 
FROM some_table 
WHERE date_time_column  BETWEEN ? AND ? + INTERVAL ? MONTH + INTERVAL ? WEEK + INTERVAL ? DAY + INTERVAL ? HOUR

This way I could use zero for parameters I don't need, for example:

  • If we need 3 hours we get something like this in pseudo-code:

      sqlx.db.Query(SELECT * FROM some_table WHERE date_time_column
                BETWEEN ? AND ? + INTERVAL ? MONTH + INTERVAL ? WEEK + INTERVAL ? DAY + INTERVAL ? HOUR,
                Start, Start, 0, 0, 0, 3)

  • If we need 3 hours and 2 days we get something like this in pseudo-code:

      sqlx.db.Query(SELECT * FROM some_table WHERE date_time_column
                BETWEEN ? AND ? + INTERVAL ? MONTH + INTERVAL ? WEEK + INTERVAL ? DAY + INTERVAL ? HOUR,
                Start, Start, 0, 0, 2, 3)

Question:

  • Is my idea above viable solution?
  • If it is not, can you advise me how to implement the above requirement?

Aside from clickhouse database, we use Golang and sqlx library to communicate with clickhouse.

sqlx clickhouse go sql
1 Answers

For me, your decision looks good.

I would simplify it a little by calculating the Stop-date outside sql-script:

import (
    "time"
)

Start := ..
Stop := Start.AddDate(0, months, days + weeks*7).Add(time.Hour * time.Duration(hours))

SELECT *
FROM some_table
WHERE date_time_column BETWEEN ? AND ?

This way looks more maintainable (not required knowledge of CH SQL) and testable (from a unit-tests point of view).

user_3032980
Related