MySQL学习笔记：生成时间维度表

最新推荐文章于 2025-05-10 02:42:31 发布

weixin_30515513

最新推荐文章于 2025-05-10 02:42:31 发布

阅读量197

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/hider/p/9103137.html

本文介绍如何使用MySQL的日期时间函数来快速生成时间维度表，包括按小时、分钟和秒生成的时间戳，并提供了完整的SQL脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

# ---- 对应时间戳怎么生成的？ ----

/*
TIME_CD TIME_CD1
000000 000005
000001 000005
000002 000005
000003 000005
000004 000005
000005 000010
*/

　　利用MySQL常用日期函数生成时间维度表

日期时间函数
按小时、按分钟、按秒生成

SELECT STR_TO_DATE('September 02, 2016','%M %d, %Y') AS DATE; # 2016-09-02

SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP;
# 2018-05-29    01:00:54     2018-05-29 01:00:54

　　MySQL快速生成时间维度表

SET @d0 = '2018-01-01';
SET @d1 = '2018-12-31';
SET @date = DATE_SUB(@d0, INTERVAL 1 DAY);

USE test;

CREATE TABLE aa_time_dim(
 DATE DATE DEFAULT NULL,
 id INT(11) NOT NULL,
 Y SMALLINT(6) DEFAULT NULL,
 m SMALLINT(6) DEFAULT NULL,
 d SMALLINT(6) DEFAULT NULL,
 yw SMALLINT(6) DEFAULT NULL,
 w SMALLINT(6) DEFAULT NULL,
 q SMALLINT(6) DEFAULT NULL,
 wd SMALLINT(6) DEFAULT NULL,
 m_name CHAR(10) DEFAULT NULL,
 wd_name CHAR(10) DEFAULT NULL,
 PRIMARY KEY (id))ENGINE = INNODB DEFAULT CHARSET = utf8;

SELECT * FROM aa_time_dim;

INSERT INTO aa_time_dim
SELECT 
    @date := DATE_ADD(@date, INTERVAL 1 DAY) AS DATE,
    DATE_FORMAT(@date, '%Y%m%d') AS id,
    YEAR(@date) AS Y,
    MONTH(@date) AS m,
    DAY(@date) AS d,
    DATE_FORMAT(@date, '%x') AS yw,
    WEEK(@date,3) AS w,
    QUARTER(@date) AS q,
    WEEKDAY(@date) + 1 AS wd,
    MONTHNAME(@date) AS m_name,
    DAYNAME(@date) AS wd_name
FROM xxx
WHERE DATE_ADD(@date,INTERVAL 1 DAY) <= @d1
ORDER BY DATE;

SELECT * FROM aa_time_dim;

DROP TABLE aa_time_dim;