如何在SQL Server中计算时间段ID并匹配双表时间区间数据

1. 关联table1与table2，筛选符合时间范围的数据 #

首先需要将table2中的Date和Time interval转换为完整的时间戳，再与table1的起止时间匹配，确保筛选出重叠时间段的数据。

SQL示例（SQL Server版本） #

SELECT 
    t1.ID AS table1_id,
    t2.Date,
    t2.Time_interval,
    t2.Amount,
    -- 生成table2记录对应的时间段起始时间
    DATEADD(minute, (t2.Time_interval - 1)*5, CONVERT(datetime, t2.Date)) AS record_start_time
FROM 
    table2 t2
JOIN 
    table1 t1 ON 
        -- 时间段起始时间不晚于table1的结束时间
        DATEADD(minute, (t2.Time_interval - 1)*5, CONVERT(datetime, t2.Date)) <= t1.[End date]
        -- 时间段结束时间早于table1的起始时间（确保有重叠）
        AND DATEADD(minute, t2.Time_interval*5, CONVERT(datetime, t2.Date)) > t1.[Start date]

针对示例数据的说明 #

以你提供的table1记录（2003-09-17 14:10:00 至 2003-09-18 14:20:00）为例，查询会返回：

• 2003-09-17中Time_interval >= 171的记录（171对应时间段起始时间为14:10:00）
• 2003-09-18中Time_interval <= 173的记录（173对应时间段结束时间为14:20:00）

2. 计算从table1起始时间开始的连续时间段ID #

如果需要的不是table2中每日重置的Time_interval，而是从table1的Start date开始计算的连续序号（比如起始时间对应的第一个5分钟段为1，下一个为2，跨天延续），可以用以下逻辑计算：

SQL示例（SQL Server版本） #

SELECT 
    t1.ID AS table1_id,
    t2.Date,
    t2.Time_interval,
    t2.Amount,
    DATEADD(minute, (t2.Time_interval - 1)*5, CONVERT(datetime, t2.Date)) AS record_start_time,
    -- 计算连续时间段ID：时间差除以5分钟后加1
    DATEDIFF(minute, t1.[Start date], DATEADD(minute, (t2.Time_interval - 1)*5, CONVERT(datetime, t2.Date))) / 5 + 1 AS continuous_interval_id
FROM 
    table2 t2
JOIN 
    table1 t1 ON 
        DATEADD(minute, (t2.Time_interval - 1)*5, CONVERT(datetime, t2.Date)) <= t1.[End date]
        AND DATEADD(minute, t2.Time_interval*5, CONVERT(datetime, t2.Date)) > t1.[Start date]

适配MySQL的版本 #

如果使用MySQL，需调整日期函数：

SELECT 
    t1.ID AS table1_id,
    t2.Date,
    t2.Time_interval,
    t2.Amount,
    DATE_ADD(STR_TO_DATE(t2.Date, '%Y-%m-%d'), INTERVAL (t2.Time_interval - 1)*5 MINUTE) AS record_start_time,
    TIMESTAMPDIFF(MINUTE, t1.`Start date`, DATE_ADD(STR_TO_DATE(t2.Date, '%Y-%m-%d'), INTERVAL (t2.Time_interval - 1)*5 MINUTE)) / 5 + 1 AS continuous_interval_id
FROM 
    table2 t2
JOIN 
    table1 t1 ON 
        DATE_ADD(STR_TO_DATE(t2.Date, '%Y-%m-%d'), INTERVAL (t2.Time_interval - 1)*5 MINUTE) <= t1.`End date`
        AND DATE_ADD(STR_TO_DATE(t2.Date, '%Y-%m-%d'), INTERVAL t2.Time_interval*5 MINUTE) > t1.`Start date`

内容的提问来源于stack exchange，提问作者radha krishna Rayudu