MYSQL查詢以獲取今天所有年份的日期-有解無憂

我有一個包含一年中每一天的資料點的表格，其中包含幾年的歷史記錄。現在我想要今天、過去 15 天和未來 15 天左右日期的資料平均值。通常這將適用于類似的東西

select average(data_point)
from my_table
where dayofyear(point_date) between dayofyear(now()) - 15 and dayofyear(now())   15

如果當前日期是 01-11-2021，那么這將給出所有年份從 10 月 16 日到 11 月 16 日的所有天數的平均值。

但是當當前日期在新年的 15 天內時，這不起作用。如果當前日期是 21-12-2021 則dayofyear(now()) 15for is370并且您只能獲得從 12 月 6 日到 12 月 31 日的日期。它應該在 12 月 6 日到 1 月 5 日回傳。

當您對 365 取模時，它也將不起作用，因為那么 dayofyear 將導致between 340 and 5并且不回傳任何結果。

任何提示如何實作這一點？

uj5u.com熱心網友回復：

我真的很困惑以前的解決方案不起作用。但是，我找到了一個我也測驗過的解決方案。您所要做的就是調整您想要查看的天數和欄位。我正在考慮是否要向 MariaDB 添加票證。

此查詢有效：

SELECT m2.* FROM (
  SELECT DISTINCT YEAR(point_date)
  , STR_TO_DATE( CONCAT( YEAR(point_date),'0101'),"%Y%c%D" )   INTERVAL DAYOFYEAR(NOW())-14 DAY AS from_date
  , STR_TO_DATE( CONCAT( YEAR(point_date),'0101'),"%Y%c%D" )   INTERVAL DAYOFYEAR(NOW()) 14 DAY AS to_date
  FROM my_table
) AS m1
INNER JOIN my_table AS m2 ON m2.`point_date` 
BETWEEN m1.`from_date` AND m1.`to_date`;

樣本

我已經創建了一個這樣的表

CREATE TABLE `cp` (
  `id` int(11) unsigned NOT NULL,
  `point_date` date DEFAULT NULL,
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

并用資料填充它（100行）

** 測驗所有欄位**

MariaDB [bernd]> SELECT * FROM (
    ->   SELECT DISTINCT YEAR(point_date)
    ->   , STR_TO_DATE( CONCAT( YEAR(point_date),'0101'),"%Y%c%D" )   INTERVAL DAYOFYEAR(NOW())-14 DAY AS from_date
    ->   , STR_TO_DATE( CONCAT( YEAR(point_date),'0101'),"%Y%c%D" )   INTERVAL DAYOFYEAR(NOW()) 14 DAY AS to_date
    ->   FROM my_table
    -> ) AS m1
    -> INNER JOIN my_table AS m2 ON m2.`point_date` 
    -> BETWEEN m1.`from_date` AND m1.`to_date`;
 ------------------ ------------ ------------ ----- ------------ 
| YEAR(point_date) | from_date  | to_date    | id  | point_date |
 ------------------ ------------ ------------ ----- ------------ 
|             2021 | 2021-12-09 | 2022-01-06 |  56 | 2022-01-06 |
|             2021 | 2021-12-09 | 2022-01-06 |  57 | 2022-01-05 |
|             2021 | 2021-12-09 | 2022-01-06 |  58 | 2022-01-04 |
|             2021 | 2021-12-09 | 2022-01-06 |  59 | 2022-01-03 |
|             2021 | 2021-12-09 | 2022-01-06 |  60 | 2022-01-02 |
|             2021 | 2021-12-09 | 2022-01-06 |  61 | 2022-01-01 |
|             2021 | 2021-12-09 | 2022-01-06 |  62 | 2021-12-31 |
|             2021 | 2021-12-09 | 2022-01-06 |  63 | 2021-12-30 |
|             2021 | 2021-12-09 | 2022-01-06 |  64 | 2021-12-29 |
|             2021 | 2021-12-09 | 2022-01-06 |  65 | 2021-12-28 |
|             2021 | 2021-12-09 | 2022-01-06 |  66 | 2021-12-27 |
|             2021 | 2021-12-09 | 2022-01-06 |  67 | 2021-12-26 |
|             2021 | 2021-12-09 | 2022-01-06 |  68 | 2021-12-25 |
|             2021 | 2021-12-09 | 2022-01-06 |  69 | 2021-12-24 |
|             2021 | 2021-12-09 | 2022-01-06 |  70 | 2021-12-23 |
|             2021 | 2021-12-09 | 2022-01-06 |  71 | 2021-12-22 |
|             2021 | 2021-12-09 | 2022-01-06 |  72 | 2021-12-21 |
|             2021 | 2021-12-09 | 2022-01-06 |  73 | 2021-12-20 |
|             2021 | 2021-12-09 | 2022-01-06 |  74 | 2021-12-19 |
|             2021 | 2021-12-09 | 2022-01-06 |  75 | 2021-12-18 |
|             2021 | 2021-12-09 | 2022-01-06 |  76 | 2021-12-17 |
|             2021 | 2021-12-09 | 2022-01-06 |  77 | 2021-12-16 |
|             2021 | 2021-12-09 | 2022-01-06 |  78 | 2021-12-15 |
|             2021 | 2021-12-09 | 2022-01-06 |  79 | 2021-12-14 |
|             2021 | 2021-12-09 | 2022-01-06 |  80 | 2021-12-13 |
|             2021 | 2021-12-09 | 2022-01-06 |  81 | 2021-12-12 |
|             2021 | 2021-12-09 | 2022-01-06 |  82 | 2021-12-11 |
|             2021 | 2021-12-09 | 2022-01-06 |  83 | 2021-12-10 |
|             2021 | 2021-12-09 | 2022-01-06 |  84 | 2021-12-09 |
|             2020 | 2020-12-08 | 2021-01-05 | 422 | 2021-01-05 |
|             2020 | 2020-12-08 | 2021-01-05 | 423 | 2021-01-04 |
|             2020 | 2020-12-08 | 2021-01-05 | 424 | 2021-01-03 |
|             2020 | 2020-12-08 | 2021-01-05 | 425 | 2021-01-02 |
|             2020 | 2020-12-08 | 2021-01-05 | 426 | 2021-01-01 |
|             2020 | 2020-12-08 | 2021-01-05 | 427 | 2020-12-31 |
|             2020 | 2020-12-08 | 2021-01-05 | 428 | 2020-12-30 |
|             2020 | 2020-12-08 | 2021-01-05 | 429 | 2020-12-29 |
|             2020 | 2020-12-08 | 2021-01-05 | 430 | 2020-12-28 |
|             2020 | 2020-12-08 | 2021-01-05 | 431 | 2020-12-27 |
|             2020 | 2020-12-08 | 2021-01-05 | 432 | 2020-12-26 |
|             2020 | 2020-12-08 | 2021-01-05 | 433 | 2020-12-25 |
|             2020 | 2020-12-08 | 2021-01-05 | 434 | 2020-12-24 |
|             2020 | 2020-12-08 | 2021-01-05 | 435 | 2020-12-23 |
|             2020 | 2020-12-08 | 2021-01-05 | 436 | 2020-12-22 |
|             2020 | 2020-12-08 | 2021-01-05 | 437 | 2020-12-21 |
|             2020 | 2020-12-08 | 2021-01-05 | 438 | 2020-12-20 |
|             2020 | 2020-12-08 | 2021-01-05 | 439 | 2020-12-19 |
|             2020 | 2020-12-08 | 2021-01-05 | 440 | 2020-12-18 |
|             2020 | 2020-12-08 | 2021-01-05 | 441 | 2020-12-17 |
|             2020 | 2020-12-08 | 2021-01-05 | 442 | 2020-12-16 |
|             2020 | 2020-12-08 | 2021-01-05 | 443 | 2020-12-15 |
|             2020 | 2020-12-08 | 2021-01-05 | 444 | 2020-12-14 |
|             2020 | 2020-12-08 | 2021-01-05 | 445 | 2020-12-13 |
|             2020 | 2020-12-08 | 2021-01-05 | 446 | 2020-12-12 |
|             2020 | 2020-12-08 | 2021-01-05 | 447 | 2020-12-11 |
|             2020 | 2020-12-08 | 2021-01-05 | 448 | 2020-12-10 |
|             2020 | 2020-12-08 | 2021-01-05 | 449 | 2020-12-09 |
|             2020 | 2020-12-08 | 2021-01-05 | 450 | 2020-12-08 |
|             2019 | 2019-12-09 | 2020-01-06 | 787 | 2020-01-06 |
|             2019 | 2019-12-09 | 2020-01-06 | 788 | 2020-01-05 |
|             2019 | 2019-12-09 | 2020-01-06 | 789 | 2020-01-04 |
|             2019 | 2019-12-09 | 2020-01-06 | 790 | 2020-01-03 |
|             2019 | 2019-12-09 | 2020-01-06 | 791 | 2020-01-02 |
|             2019 | 2019-12-09 | 2020-01-06 | 792 | 2020-01-01 |
|             2019 | 2019-12-09 | 2020-01-06 | 793 | 2019-12-31 |
|             2019 | 2019-12-09 | 2020-01-06 | 794 | 2019-12-30 |
|             2019 | 2019-12-09 | 2020-01-06 | 795 | 2019-12-29 |
|             2019 | 2019-12-09 | 2020-01-06 | 796 | 2019-12-28 |
|             2019 | 2019-12-09 | 2020-01-06 | 797 | 2019-12-27 |
|             2019 | 2019-12-09 | 2020-01-06 | 798 | 2019-12-26 |
|             2019 | 2019-12-09 | 2020-01-06 | 799 | 2019-12-25 |
|             2019 | 2019-12-09 | 2020-01-06 | 800 | 2019-12-24 |
|             2019 | 2019-12-09 | 2020-01-06 | 801 | 2019-12-23 |
|             2019 | 2019-12-09 | 2020-01-06 | 802 | 2019-12-22 |
|             2019 | 2019-12-09 | 2020-01-06 | 803 | 2019-12-21 |
|             2019 | 2019-12-09 | 2020-01-06 | 804 | 2019-12-20 |
|             2019 | 2019-12-09 | 2020-01-06 | 805 | 2019-12-19 |
|             2019 | 2019-12-09 | 2020-01-06 | 806 | 2019-12-18 |
|             2019 | 2019-12-09 | 2020-01-06 | 807 | 2019-12-17 |
|             2019 | 2019-12-09 | 2020-01-06 | 808 | 2019-12-16 |
|             2019 | 2019-12-09 | 2020-01-06 | 809 | 2019-12-15 |
|             2019 | 2019-12-09 | 2020-01-06 | 810 | 2019-12-14 |
|             2019 | 2019-12-09 | 2020-01-06 | 811 | 2019-12-13 |
|             2019 | 2019-12-09 | 2020-01-06 | 812 | 2019-12-12 |
|             2019 | 2019-12-09 | 2020-01-06 | 813 | 2019-12-11 |
|             2019 | 2019-12-09 | 2020-01-06 | 814 | 2019-12-10 |
|             2019 | 2019-12-09 | 2020-01-06 | 815 | 2019-12-09 |
 ------------------ ------------ ------------ ----- ------------ 
87 rows in set (0.01 sec)

MariaDB [bernd]>

請讓我知道它是否適合您

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/390165.html

標籤：mysql 日期

上一篇：我怎樣才能從現在到每周通過碳獲得星期三的日期？[復制]

下一篇：按多欄位打字稿排序