TDengine 时间函数 WEEKOFYEAR() 用户手册

发布于：2025-09-09 ⋅ 阅读:(18) ⋅ 点赞:(0)

在这里插入图片描述

WEEKOFYEAR() 函数用户手册

函数定义

WEEKOFYEAR(expr)

功能说明

WEEKOFYEAR() 函数返回输入日期在一年中的第几周，采用 ISO 8601 标准的周数计算方式。该函数等同于 WEEK(expr, 3)，即每周第一天是周一，第一个包含四天及以上的周为第 1 周。

版本要求

  • 最低版本: v3.3.3.0

返回值

  • 数据类型: BIGINT
  • 取值范围: 1-53
  • NULL 处理: 当输入为 NULL 时，返回 NULL

适用数据类型

支持以下类型的输入参数：

输入类型 说明 示例
TIMESTAMP 时间戳类型 '2024-01-01 12:00:00'
BIGINT 表示时间戳的整数 1704067200000
VARCHAR 符合 ISO8601/RFC3339 标准的日期时间字符串 '2024-01-01', '2024-01-01T12:00:00Z'
NCHAR 符合 ISO8601/RFC3339 标准的日期时间字符串 '2024-01-01 12:00:00'

适用范围

  • 表类型: 表和超级表
  • 查询支持: 内层查询和外层查询
  • 时间精度: 输入时间戳的精度由所查询表的精度确定，若未指定表，则精度为毫秒

周数计算规则

根据 ISO 8601 标准：

  • 每周第一天是周一
  • 第一个包含四天及以上的周为第 1 周
  • 返回值范围为 1-53
  • 年初或年末可能出现跨年的周数情况

基本用法示例

字符串日期格式

-- 基本日期字符串
SELECT WEEKOFYEAR('2024-01-01');
-- 返回: 1 (2024年第1周)

SELECT WEEKOFYEAR('2024-12-31');
-- 返回: 1 (2025年第1周，因为该周大部分天数在2025年)

SELECT WEEKOFYEAR('2000-01-01');
-- 返回: 52 (1999年第52周，因为该周大部分天数在1999年)

-- ISO8601 格式
SELECT WEEKOFYEAR('2024-06-15T10:30:00Z');
-- 返回: 24 (2024年第24周)

时间戳格式

-- TIMESTAMP 类型
SELECT WEEKOFYEAR(TIMESTAMP '2024-01-01 08:00:00');
-- 返回: 1

-- BIGINT 时间戳（毫秒）
SELECT WEEKOFYEAR(1704067200000);
-- 返回: 1

NULL 值处理

SELECT WEEKOFYEAR(NULL);
-- 返回: NULL

智能电表场景应用示例

基于智能电表数据库结构：

-- 数据库和表结构
USE test;
-- meters 超级表包含 ts, current, voltage, phase 字段和 location, groupid 标签

场景1：按周统计电力消耗

-- 按周统计每周的用电情况
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    COUNT(*) as record_count,
    AVG(current) as avg_current,
    MAX(current) as peak_current,
    MIN(current) as min_current,
    AVG(voltage) as avg_voltage,
    location
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location
ORDER BY week_of_year, location;

场景2：季度用电趋势分析

-- 按季度分析不同周的用电模式
SELECT 
    CASE 
        WHEN WEEKOFYEAR(ts) BETWEEN 1 AND 13 THEN 'Q1'
        WHEN WEEKOFYEAR(ts) BETWEEN 14 AND 26 THEN 'Q2'
        WHEN WEEKOFYEAR(ts) BETWEEN 27 AND 39 THEN 'Q3'
        ELSE 'Q4'
    END as quarter,
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(current) as avg_current,
    MAX(voltage) as max_voltage,
    MIN(voltage) as min_voltage
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY 
    CASE 
        WHEN WEEKOFYEAR(ts) BETWEEN 1 AND 13 THEN 'Q1'
        WHEN WEEKOFYEAR(ts) BETWEEN 14 AND 26 THEN 'Q2'
        WHEN WEEKOFYEAR(ts) BETWEEN 27 AND 39 THEN 'Q3'
        ELSE 'Q4'
    END,
    WEEKOFYEAR(ts), 
    location
ORDER BY quarter, week_of_year, location;

场景3：假期周用电模式识别

-- 识别假期周的用电模式
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(current) as avg_current,
    CASE 
        WHEN WEEKOFYEAR(ts) IN (1, 2) THEN '元旦假期'
        WHEN WEEKOFYEAR(ts) BETWEEN 6 AND 8 THEN '春节假期'
        WHEN WEEKOFYEAR(ts) IN (18, 19) THEN '五一假期'
        WHEN WEEKOFYEAR(ts) IN (40, 41) THEN '国庆假期'
        ELSE '正常周'
    END as period_type,
    COUNT(*) as record_count
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location
ORDER BY week_of_year, location;

场景4：电网负载分析

-- 分析不同周的负载情况
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    COUNT(DISTINCT location) as location_count,
    AVG(current) as overall_avg_current,
    MAX(current) as max_current,
    MIN(current) as min_current
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts)
HAVING COUNT(DISTINCT location) >= 2
ORDER BY week_of_year;

场景5：维护计划优化

-- 基于历史周数据制定维护计划
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(current) as avg_current,
    MAX(voltage) - MIN(voltage) as voltage_range,
    CASE 
        WHEN AVG(current) < 12.0 THEN '最佳维护窗口'
        WHEN AVG(current) < 14.0 THEN '适合维护'
        WHEN AVG(current) > 16.0 THEN '避免维护'
        ELSE '谨慎安排'
    END as maintenance_recommendation
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location
HAVING COUNT(*) >= 20
ORDER BY location, avg_current ASC;

场景6：找出用电量异常的周

-- 找出平均用电量超过15A的周
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(current) as avg_current,
    MAX(current) as peak_current,
    COUNT(*) as record_count
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location
HAVING AVG(current) > 15.0
ORDER BY avg_current DESC;

场景7：按周进行电压稳定性分析

-- 分析每周的电压稳定性
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(voltage) as avg_voltage,
    MAX(voltage) as max_voltage,
    MIN(voltage) as min_voltage,
    CASE 
        WHEN (MAX(voltage) - MIN(voltage)) < 5 THEN '电压稳定'
        WHEN (MAX(voltage) - MIN(voltage)) < 10 THEN '轻微波动'
        ELSE '电压不稳定'
    END as voltage_stability
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location
HAVING COUNT(*) >= 10
ORDER BY (MAX(voltage) - MIN(voltage)) DESC;

场景8：工作日vs周末的对比分析

-- 结合WEEKDAY函数分析工作日vs周末在不同周的表现
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    location,
    CASE 
        WHEN WEEKDAY(ts) IN (5, 6) THEN '周末'
        ELSE '工作日'
    END as day_type,
    AVG(current) as avg_current,
    COUNT(*) as record_count
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
GROUP BY WEEKOFYEAR(ts), location, 
    CASE 
        WHEN WEEKDAY(ts) IN (5, 6) THEN '周末'
        ELSE '工作日'
    END
ORDER BY week_of_year, location, day_type;

场景9：特定周数的详细监控

-- 监控特定周数的用电情况
SELECT 
    WEEKOFYEAR(ts) as week_of_year,
    WEEKDAY(ts) as weekday,
    CASE WEEKDAY(ts)
        WHEN 0 THEN '星期一'
        WHEN 1 THEN '星期二'
        WHEN 2 THEN '星期三'
        WHEN 3 THEN '星期四'
        WHEN 4 THEN '星期五'
        WHEN 5 THEN '星期六'
        WHEN 6 THEN '星期日'
    END as weekday_name,
    location,
    AVG(current) as daily_avg_current,
    MAX(current) as daily_peak_current,
    COUNT(*) as daily_record_count
FROM meters 
WHERE WEEKOFYEAR(ts) = 20 
GROUP BY WEEKOFYEAR(ts), WEEKDAY(ts), location
ORDER BY weekday, location;

场景10：周级别的用电趋势分析

-- 分析前10周vs后10周的用电趋势
SELECT 
    CASE 
        WHEN WEEKOFYEAR(ts) <= 10 THEN '年初10周'
        WHEN WEEKOFYEAR(ts) >= 43 THEN '年末10周'
        ELSE '其他时期'
    END as `period`,
    WEEKOFYEAR(ts) as week_of_year,
    location,
    AVG(current) as avg_current,
    COUNT(*) as record_count
FROM meters 
WHERE ts >= '2024-01-01' AND ts < '2025-01-01'
    AND (WEEKOFYEAR(ts) <= 10 OR WEEKOFYEAR(ts) >= 43)
GROUP BY 
    CASE 
        WHEN WEEKOFYEAR(ts) <= 10 THEN '年初10周'
        WHEN WEEKOFYEAR(ts) >= 43 THEN '年末10周'
        ELSE '其他时期'
    END,
    WEEKOFYEAR(ts), 
    location
ORDER BY `period`, week_of_year, location;

与 WEEK() 函数的关系

-- WEEKOFYEAR() 等同于 WEEK(expr, 3)
SELECT 
    ts,
    WEEKOFYEAR(ts) as weekofyear_result,
    WEEK(ts, 3) as week_mode3_result
FROM meters 
WHERE ts >= '2024-01-01' AND ts <= '2024-01-07'
LIMIT 10;

TDengine 中的时间函数

在智能电表场景中，常用的 TDengine 时间函数：

-- 基本时间函数示例
SELECT 
    ts,
    WEEKOFYEAR(ts) as week,      -- 周数(1-53)
    WEEKDAY(ts) as weekday,      -- 星期几(0-6)
    NOW() as current_time,       -- 当前时间
    TODAY() as today_start       -- 今日零时
FROM meters 
LIMIT 5;

注意事项

  1. ISO 8601 标准: 函数采用 ISO 8601 周数计算标准
  2. 跨年处理: 年初和年末的周数可能属于前一年或后一年
  3. 时区影响: 函数基于输入时间的字面值计算，不考虑时区转换
  4. 精度处理: 时间戳精度由查询表的精度设置决定
  5. TDengine 语法限制:
    • 使用简单的聚合函数和 GROUP BY
    • 避免复杂的子查询和窗口函数
    • 不支持某些标准 SQL 的日期函数

相关函数

关于 TDengine

TDengine 专为物联网IoT平台、工业大数据平台设计。其中，TDengine TSDB 是一款高性能、分布式的时序数据库（Time Series Database），同时它还带有内建的缓存、流式计算、数据订阅等系统功能；TDengine IDMP 是一款AI原生工业数据管理平台，它通过树状层次结构建立数据目录，对数据进行标准化、情景化，并通过 AI 提供实时分析、可视化、事件管理与报警等功能。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布