Mysql-通过help_topic生成日期

一 获取某个时间段所有日期

SELECT 
  DATE_FORMAT(
    DATE_ADD(
      CONCAT('2021-10-01'),
      INTERVAL (CAST(help_topic_id AS SIGNED)) DAY
    ),
    '%Y-%m-%d'
  ) DATE 
FROM
  mysql.help_topic 
WHERE help_topic_id < TIMESTAMPDIFF(
    DAY,
    CONCAT(
      STR_TO_DATE('2021-10-01', '%Y-%m-%d')
    ),
    CONCAT(
      STR_TO_DATE('2021-12-01', '%Y-%m-%d')
    )
  )

运行结果

二 获取某年每个月(2021-01至2021-12)以及月号(1至12)

这里数据库版本不同，sql需要不同版本的修改

###5.7.28-log
SELECT 
  DATE_FORMAT(
    DATE_ADD(
      CONCAT(YEAR(NOW()), '-01-01'),
      INTERVAL (CAST(help_topic_id AS SIGNED)) MONTH
    ),
    '%Y-%m'
  ) DATE,
  DATE_FORMAT(
    DATE_ADD(
      CONCAT(YEAR(NOW()), '-01-01'),
      INTERVAL (CAST(help_topic_id AS SIGNED)) MONTH
    ),
    '%c'
  ) MONTH 
FROM
  mysql.help_topic 
WHERE help_topic_id < 12 
ORDER BY DATE ASC

运行结果

###10.5.10-MariaDB-log
SELECT 
  DATE_FORMAT(
    DATE_ADD(
      CONCAT(YEAR(NOW()), '-01-01'),
      INTERVAL (CAST(help_topic_id AS SIGNED) -1) MONTH
    ),
    '%Y-%m'
  ) DATE,
  DATE_FORMAT(
    DATE_ADD(
      CONCAT(YEAR(NOW()), '-01-01'),
      INTERVAL (CAST(help_topic_id AS SIGNED) -1) MONTH
    ),
    '%c'
  ) MONTH 
FROM
  mysql.help_topic 
WHERE help_topic_id <= 12 
ORDER BY DATE ASC

运行结果

三 一天24H

SELECT 
  DATE_FORMAT(
    DATE_ADD(
      NOW(),
      INTERVAL (CAST(help_topic_id AS SIGNED)) HOUR
    ),
    '%H'
  ) DATE 
FROM
  mysql.help_topic 
WHERE help_topic_id < 24 
ORDER BY DATE ASC 

运行结果

四 一天24H 获取寄时/偶时

SELECT
date_table.date AS dateHour
FROM(
SELECT
DATE_FORMAT(
DATE_ADD(
NOW(),
INTERVAL (CAST( help_topic_id AS SIGNED )) HOUR ),'%H'
) DATE
FROM mysql.help_topic
WHERE help_topic_id <= 24
ORDER BY DATE ASC
) date_table
WHERE MOD(CONVERT(date_table.DATE,SIGNED), 2)=1

运行结果

SELECT
date_table.date AS dateHour
FROM(
SELECT
DATE_FORMAT(
DATE_ADD(
NOW(),
INTERVAL (CAST( help_topic_id AS SIGNED )) HOUR ),'%H'
) DATE
FROM mysql.help_topic
WHERE help_topic_id <= 24
ORDER BY DATE ASC
) date_table
WHERE MOD(CONVERT(date_table.DATE,SIGNED), 2)=0

运行结果