Visualization of Reservation Counts in 10-Minute Intervals Throughout the Day

Get Help
1

Hello,

I have a fact table "BOOKNGS" that contains, among other things, the reservation date (datetime) and the amount.

I would like to display the number of reservations made in 10-minute intervals throughout the day.

I confess I don't know how to approach this. Any help would be welcome.

Best Regards,

2

Hi Jerpic, are you using SQL?

in PostgreSQL you can use the following snippet:

SELECT 
        date_trunc('hour', reservation_date_column ) +  INTERVAL '10 minutes' * FLOOR(EXTRACT(MINUTE 
             FROM reservation_date_column) / 10) as formatted_time,
       count(distinct reservation_id_column) as reservation_counts
FROM BOOKNGS
GROUP BY 1;

This query first truncates every datetime value to the hour component, and then extracts the minute quantity, divides it by 10 and floors the product of the division.

This gives you the amount of times that 10 minutes can fit into the hour component. after that step, it multiplies this resulting number by an interval of 10 minutes and adds that to the previously truncated hour value.

the result is a column with 10min increment in their timestamps, which can then be aggregated using group by.

In Mysql the process is similar

SELECT
        TIMESTAMP(FLOOR(UNIX_TIMESTAMP(reservation_date_column) / 600) * 600) as formatted_timestamp,
       count(distinct reservation_id_column) as reservation_counts
FROM BOOKNG;

MYSQL does not have a built-in date_trunc function, so instead, you take the timestamp column and convert into an EPOCH time value (quantity of seconds since 01/01/1970).

Divide it by 600 (the amount of seconds in 10min) and floor the result, so you know how many complete 10 minute intervals fit in your epoch value. once that's done, you can now multiply it by 600, which will give you the exact epoch of the last completel 10min interval. the TIMESTAMP function turns it back into a timestamp.