Created
July 29, 2022 12:33
-
-
Save nitaking/4a9f9c2bb62e0c9d04c5d36c85adb148 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select id, | |
start_at, | |
# date_add(start_at, interval -DAY(start_at) + 1 DAY), | |
end_at | |
from sample_data; | |
# [ | |
# { | |
# "id": 1, | |
# "start_at": "2022-04-01 20:57:07", | |
# "end_at": "2022-07-26 20:57:21" | |
# }, | |
# { | |
# "id": 2, | |
# "start_at": "2022-03-04 20:57:07", | |
# "end_at": "2022-03-26 20:57:21" | |
# }, | |
# { | |
# "id": 3, | |
# "start_at": "2022-06-16 20:57:07", | |
# "end_at": "2022-07-20 20:57:07" | |
# } | |
# ] | |
select id, date_format(month, '%Y%m') | |
from sample_data | |
# 区切りたい日付単位のテーブルとinner joinで掛け算状態にする | |
inner join (SELECT '2022-03-01' as month | |
UNION | |
SELECT '2022-04-01' as month | |
UNION | |
SELECT '2022-05-01' as month | |
UNION | |
SELECT '2022-06-01' as month | |
UNION | |
SELECT '2022-07-01' as month) as months | |
on month | |
between | |
# monthsが月初めの日付なので、start_atの月初日を取得する | |
DATE(date_add(start_at, interval -DAY(start_at) + 1 DAY)) | |
and end_at | |
order by id, month asc | |
; | |
# [ | |
# { | |
# "id": 1, | |
# "date_format(month, '%Y%m')": "202204" | |
# }, | |
# { | |
# "id": 1, | |
# "date_format(month, '%Y%m')": "202205" | |
# }, | |
# { | |
# "id": 1, | |
# "date_format(month, '%Y%m')": "202206" | |
# }, | |
# { | |
# "id": 1, | |
# "date_format(month, '%Y%m')": "202207" | |
# }, | |
# { | |
# "id": 2, | |
# "date_format(month, '%Y%m')": "202203" | |
# }, | |
# { | |
# "id": 3, | |
# "date_format(month, '%Y%m')": "202206" | |
# }, | |
# { | |
# "id": 3, | |
# "date_format(month, '%Y%m')": "202207" | |
# } | |
# ] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
see https://stackoverflow.com/a/54068381