In data processing workflows, it is often necessary to normalize timestamps to the begining of a specific time bucket. For instance, if you are aggregating metrics in 30-minute windows, a timestamp like 2024-03-26 14:25:59 should be normalized to 2024-03-26 14:00:00. This article demonstrates how to achieve this floor-operation in Python using Pandas, as well as in ClickHouse and PostgreSQL.

Python Implementation with Pandas

The following approach uses a custom function to calculate the floored time. It determines the current minute, divides it by the interval size, and reconstructs the datetime object.

import pandas as pd

# Sample dataset
records = pd.DataFrame({
    'event_time': ['2024-03-26 07:00:00', '2024-03-26 07:15:00', '2024-03-26 07:29:59', '2024-03-26 07:50:00'],
    'metric': [10, 20, 30, 40]
})

def floor_to_interval(timestamp, span=30):
    """
    Rounds a datetime down to the nearest interval (e.g., 30 minutes).
    
    :param timestamp: pandas.Timestamp object
    :param span: The minute interval size (e.g., 5, 10, 30)
    :return: Normalized pandas.Timestamp
    """
    bucket_minute = (timestamp.minute // span) * span
    return timestamp.replace(minute=bucket_minute, second=0, microsecond=0)

# Convert column to datetime
records['event_time'] = pd.to_datetime(records['event_time'])

# Apply the normalization function
records['time_bucket'] = records['event_time'].apply(lambda t: floor_to_interval(t, span=30))
records

SQL Implementations

ClickHouse

ClickHouse provides a native function toStartOfInterval which simplifies this task significantly. You can specify the interval direct within the function call.

-- Normalizing a specific timestamp to a 30-minute window
SELECT toStartOfInterval(toDateTime('2024-02-10 12:13:49'), INTERVAL 30 MINUTE) AS window_start;

-- Applying to a table column
SELECT 
    event_time,
    toStartOfInterval(event_time, INTERVAL 30 MINUTE) AS window_start
FROM events_table;

PostgreSQL

PostgreSQL does not have a direct equivalent to ClickHouse's function, but you can combine date_trunc with itnerval arithmetic to achieve the same result. The strategy is to truncate to the hour and then add the floored minute difference.

-- Normalize '2024-12-12 12:35:31' to the start of a 30-minute interval
SELECT 
    timestamp '2024-12-12 12:35:31' AS original_time,
    date_trunc('hour', timestamp '2024-12-12 12:35:31') + 
    INTERVAL '30 min' * (EXTRACT(MINUTE FROM timestamp '2024-12-12 12:35:31')::int / 30) AS window_start;