📚 课程目录第4章时间序列数据基础

4. 时间序列数据基础：时间戳概念、时间数据类型与常见时间函数

时间序列分析的核心是“时间”本身。在 SQL 与 Grafana 的结合中，对时间戳的准确理解与高效操作，直接决定了趋势分析的质量。本章将系统梳理时间戳概念、主流数据库的时间数据类型，以及最常用的时间函数（DATE_TRUNC、EXTRACT、NOW），为后续构建趋势仪表盘打下坚实基础。

4.1 时间戳概念：从时刻到时间序列

时间戳（Timestamp）是时间序列数据中最基本的元素，它代表一个的、唯一的时刻。在数据库中，时间戳通常以两种形式存在：

Unix 时间戳：从 1970-01-01 00:00:00 UTC 开始经过的秒数（或毫秒数），为整数类型，存储效率高。
日期时间类型：如 TIMESTAMP、DATETIME，以可读的字符串形式存储，包含年月日时分秒。

关键认知：Grafana 在查询数据时，要求结果集中必须包含一个名为 time 的列（或通过别名映射），该列的值可以是 Unix 时间戳（秒或毫秒）或标准日期时间字符串。Grafana 会自动将其转换为仪表盘时间轴上的点。

4.2 常见数据库时间数据类型对比

不同数据库对时间数据的存储类型命名与行为存在差异。在编写跨数据库兼容的查询时，需要了解这些差异。下表列出了主流数据库的时间数据类型：

数据库	日期+时间类型	仅日期类型	仅时间类型	时区支持
PostgreSQL	`TIMESTAMP` / `TIMESTAMPTZ`	`DATE`	`TIME` / `TIMETZ`	通过 `TZ` 后缀支持
MySQL	`DATETIME` / `TIMESTAMP`	`DATE`	`TIME`	仅 `TIMESTAMP` 受时区影响
ClickHouse	`DateTime` / `DateTime64`	`Date`	无独立时间类型	通过 `DateTime` 时区参数
SQLite	`TEXT` / `INTEGER` / `REAL`	无专用类型	无专用类型	需手动处理

常见陷阱：MySQL 的 TIMESTAMP 类型范围仅到 2038-01-19，且受时区设置影响；而 DATETIME 范围更广（1000-01-01 到 9999-12-31），但不涉及时区转换。在存储历史超过 20 年的数据时，优先选择 DATETIME 或 PostgreSQL 的 TIMESTAMP。

4.3 核心时间函数详解

在趋势分析中，最常用的三个时间函数是 DATE_TRUNC、EXTRACT 和 NOW。它们分别解决“时间粒度归整”、“提取时间分量”和“获取当前时间”三个核心需求。

4.3.1 DATE_TRUNC：时间粒度归整

DATE_TRUNC 函数将时间戳截断到指定的精度（如年、月、日、小时），是生成趋势图时最常用的聚合辅助函数。通过它，可以将秒级数据归整到分钟、小时或天级别，从而实现按时间窗口聚合。

语法（PostgreSQL / ClickHouse 通用）：

DATE_TRUNC('<精度单位>', <时间列>)

常用精度单位：

'hour' — 归整到小时（分钟和秒置零）
'day' — 归整到天（时分秒置零）
'week' — 归整到周起始日（周一）
'month' — 归整到月起始日
'year' — 归整到年起始日

示例：按小时统计订单数量

SELECT
    DATE_TRUNC('hour', order_time) AS hour_bucket,
    COUNT(*) AS order_count
FROM orders
WHERE order_time >= NOW() - INTERVAL '7 days'
GROUP BY hour_bucket
ORDER BY hour_bucket;

  Grafana 最佳实践：在 Grafana 的查询编辑器中，将 $__timeFilter 与 DATE_TRUNC 结合使用，可以自动利用仪表盘的时间范围选择器，实现动态趋势图。

4.3.2 EXTRACT：提取时间分量

EXTRACT 函数从时间戳中提取指定的部分，如年份、月份、星期几、小时等。它常用于按时间维度进行分组或筛选，例如分析“一周中哪天的销量最高”或“哪个小时的访问量最大”。

语法：

EXTRACT(<字段> FROM <时间列>)

常用提取字段：

字段名	返回值	示例（时间：2024-11-15 14:30:45）
`YEAR`	四位年份	2024
`MONTH`	1-12	11
`DAY`	1-31	15
`DOW`	0（周日）到 6（周六）	5（周五）
`HOUR`	0-23	14
`MINUTE`	0-59	30

示例：按星期几分组统计用户活跃度

SELECT
    EXTRACT(DOW FROM login_time) AS day_of_week,
    COUNT(DISTINCT user_id) AS active_users
FROM user_logins
WHERE login_time >= NOW() - INTERVAL '30 days'
GROUP BY day_of_week
ORDER BY day_of_week;

4.3.3 NOW：获取当前时间

NOW() 函数返回数据库服务器的当前日期时间。在趋势分析中，它主要用于动态计算时间范围，例如“最近 7 天”、“本月至今”等。

常见用法：

NOW() — 当前完整时间戳
CURRENT_DATE — 当前日期（时分秒为 00:00:00）
NOW() - INTERVAL '7 days' — 7 天前的时间点
DATE_TRUNC('month', NOW()) — 本月起始日

时区注意事项：NOW() 返回的是数据库服务器的本地时间。如果 Grafana 与数据库服务器位于不同时区，建议使用 NOW() AT TIME ZONE 'UTC' 统一为 UTC 时间，避免仪表盘显示的时间与预期不符。

4.4 综合案例：构建小时级趋势查询

将上述三个函数结合，可以构建一个完整的趋势分析查询。以下示例统计过去 24 小时内，每小时的 API 请求数量：

SELECT
    DATE_TRUNC('hour', request_time) AS time_bucket,
    COUNT(*) AS request_count,
    EXTRACT(HOUR FROM request_time) AS hour_of_day
FROM api_requests
WHERE request_time >= NOW() - INTERVAL '24 hours'
GROUP BY time_bucket, hour_of_day
ORDER BY time_bucket;

在 Grafana 中，将此查询配置为时间序列面板的数据源，将 time_bucket 映射为时间轴，request_count 映射为指标值，即可生成平滑的趋势折线图。

  进阶技巧：当数据量极大时，可以在 DATE_TRUNC 的结果上建立索引，显著提升按时间窗口聚合的查询性能。例如在 PostgreSQL 中：CREATE INDEX idx_trunc_hour ON api_requests (DATE_TRUNC('hour', request_time));

4.5 本章小结

时间戳是时间序列数据的基石，Grafana 要求结果集中包含 time 列。
不同数据库的时间数据类型存在差异，需根据实际场景选择合适类型。
DATE_TRUNC 用于时间粒度归整，是趋势聚合的核心工具。
EXTRACT 用于提取时间分量，适合周期性分析。
NOW 用于动态时间范围计算，结合 INTERVAL 实现灵活的时间窗口。