如何基于datetime字段创建time_of_day时段分类列？

以下是不同场景下的具体实现方式：

1. MySQL/MariaDB 场景 #

通过CASE WHEN语句结合HOUR()函数提取小时，可选择直接修改原表或查询时动态生成结果：

方式一：新增并更新time_of_day列 #

-- 先新增列
ALTER TABLE your_table ADD COLUMN time_of_day VARCHAR(10);

-- 更新列值
UPDATE your_table
SET time_of_day = CASE
    WHEN HOUR(datetime_column) BETWEEN 5 AND 10 THEN '上午'
    WHEN HOUR(datetime_column) BETWEEN 11 AND 16 THEN '下午'
    WHEN HOUR(datetime_column) BETWEEN 17 AND 22 THEN '晚上'
    ELSE '深夜' -- 覆盖23点到次日4点的情况
END;

方式二：查询时动态生成（不修改原表） #

SELECT
    datetime_column,
    CASE
        WHEN HOUR(datetime_column) BETWEEN 5 AND 10 THEN '上午'
        WHEN HOUR(datetime_column) BETWEEN 11 AND 16 THEN '下午'
        WHEN HOUR(datetime_column) BETWEEN 17 AND 22 THEN '晚上'
        ELSE '深夜'
    END AS time_of_day
FROM your_table;

2. PostgreSQL 场景 #

使用EXTRACT(HOUR FROM datetime_column)提取小时，搭配CASE逻辑实现：

新增并更新列 #

-- 新增列
ALTER TABLE your_table ADD COLUMN time_of_day VARCHAR(10);

-- 更新值
UPDATE your_table
SET time_of_day = CASE
    WHEN EXTRACT(HOUR FROM datetime_column) >=5 AND EXTRACT(HOUR FROM datetime_column) <11 THEN '上午'
    WHEN EXTRACT(HOUR FROM datetime_column) >=11 AND EXTRACT(HOUR FROM datetime_column) <17 THEN '下午'
    WHEN EXTRACT(HOUR FROM datetime_column) >=17 AND EXTRACT(HOUR FROM datetime_column) <23 THEN '晚上'
    ELSE '深夜'
END;

动态查询生成 #

SELECT
    datetime_column,
    CASE
        WHEN EXTRACT(HOUR FROM datetime_column) >=5 AND EXTRACT(HOUR FROM datetime_column) <11 THEN '上午'
        WHEN EXTRACT(HOUR FROM datetime_column) >=11 AND EXTRACT(HOUR FROM datetime_column) <17 THEN '下午'
        WHEN EXTRACT(HOUR FROM datetime_column) >=17 AND EXTRACT(HOUR FROM datetime_column) <23 THEN '晚上'
        ELSE '深夜'
    END AS time_of_day
FROM your_table;

3. Python Pandas 场景 #

如果是用Pandas处理内存中的数据表，可通过提取小时后映射时段：

import pandas as pd
import numpy as np

# 假设df是你的数据表，datetime_column是已解析为datetime类型的列
df['hour'] = df['datetime_column'].dt.hour

# 定义条件和对应时段
conditions = [
    (df['hour'] >=5) & (df['hour'] <11),
    (df['hour'] >=11) & (df['hour'] <17),
    (df['hour'] >=17) & (df['hour'] <23)
]
choices = ['上午', '下午', '晚上']

# 生成time_of_day列，不满足的条件默认设为'深夜'
df['time_of_day'] = np.select(conditions, choices, default='深夜')

# 可选：删除临时的hour列
df.drop('hour', axis=1, inplace=True)

内容的提问来源于stack exchange，提问作者dlopez