HIVE如何转换指定格式Timestamp并按昼夜分组对数值求和

你手里的2021-09-01T00:16:18.971228-03:00是符合ISO 8601标准的带时区偏移的时间戳，核心处理逻辑是先将字符串解析为带时区的时间对象，再提取日期、小时字段，按规则划分昼夜后聚合求和。以下是不同常用场景的实现方案：

1. Python实现 #

• 第一步：解析时间戳，直接用内置datetime模块即可完成，无需额外安装依赖

from datetime import datetime

# 单条时间戳解析示例
ts_str = "2021-09-01T00:16:18.971228-03:00"
ts = datetime.fromisoformat(ts_str)

# 提取日期、小时字段
date_part = ts.date() # 输出结果：datetime.date(2021, 9, 1)
hour_part = ts.hour # 输出结果：0

• 第二步：划分昼夜时段（示例默认白天为6:00-18:00，其余为夜间，可根据实际需求调整时段范围）

def get_time_period(hour):
    if 6 <= hour < 18:
        return "白天"
    else:
        return "夜间"

• 第三步：批量分组求和示例，假设你有一组包含时间戳和对应数值的数据集：

# 示例数据格式：[(时间戳字符串, 对应数值), ...]
raw_data = [
    ("2021-09-01T00:16:18.971228-03:00", 10),
    ("2021-09-01T12:30:00.123-03:00", 20),
    ("2021-09-02T20:00:00.456-03:00", 15)
]

sum_result = {}
for ts_str, val in raw_data:
    ts = datetime.fromisoformat(ts_str)
    current_date = ts.date()
    current_period = get_time_period(ts.hour)
    group_key = (current_date, current_period)
    sum_result[group_key] = sum_result.get(group_key, 0) + val

最终sum_result会输出按「日期+昼夜」分组的求和结果。

2. SQL实现（以MySQL 8.0+为例） #

• 单字段提取逻辑：

SELECT 
  -- 提取日期
  DATE(CONVERT_TZ(STR_TO_DATE(LEFT(ts, 26), '%Y-%m-%dT%H:%i:%s.%f'), SUBSTRING(ts, 27), '+00:00')) AS date_part,
  -- 提取小时
  HOUR(CONVERT_TZ(STR_TO_DATE(LEFT(ts, 26), '%Y-%m-%dT%H:%i:%s.%f'), SUBSTRING(ts, 27), '+00:00')) AS hour_part,
  -- 划分昼夜
  CASE WHEN HOUR(CONVERT_TZ(STR_TO_DATE(LEFT(ts, 26), '%Y-%m-%dT%H:%i:%s.%f'), SUBSTRING(ts, 27), '+00:00')) BETWEEN 6 AND 17 THEN '白天' ELSE '夜间' END AS time_period,
  value
FROM your_table;

• 聚合求和逻辑：

SELECT 
  DATE(CONVERT_TZ(STR_TO_DATE(LEFT(ts, 26), '%Y-%m-%dT%H:%i:%s.%f'), SUBSTRING(ts, 27), '+00:00')) AS date_part,
  CASE WHEN HOUR(CONVERT_TZ(STR_TO_DATE(LEFT(ts, 26), '%Y-%m-%dT%H:%i:%s.%f'), SUBSTRING(ts, 27), '+00:00')) BETWEEN 6 AND 17 THEN '白天' ELSE '夜间' END AS time_period,
  SUM(value) AS total_value
FROM your_table
GROUP BY date_part, time_period;

3. Excel实现 #

假设时间戳存储在A1单元格：

• 提取日期：公式为=DATEVALUE(LEFT(A1,10))
• 提取小时：公式为=HOUR(MID(A1,12,8))
• 划分昼夜：公式为=IF(AND(B1>=6,B1<18),"白天","夜间")，其中B1为小时字段所在单元格
• 批量求和直接用SUMIFS函数，按日期和昼夜两个条件聚合即可。

注：如果你的昼夜划分规则和示例不同，直接修改对应时段的判断逻辑即可。

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