Mysql split time-CodePudding

Need to a period of time using SQL statements monthly split,
Metadata is similar to the following:
The 2018-03-02 2018-06-02

Broken down into:
The 2018-03-02 2018-03-31
The 2018-04-01 2018-04-30
The 2018-05-01 2018-05-31
The 2018-06-01 2018-06-02

CodePudding user response:

 SELECT dt1, dt2 FROM (
SELECT 
@ dt1 as dt1, 
@ dt1:=@ dt1 - INTERVAL EXTRACT (DAY FROM @ dt1) - 1 DAY + 1 MONTH INTERVAL, 
@ dt:=@ dt1 - INTERVAL 1 DAY, 
The CASE WHEN the @ dt1 & lt; @ dt2 THEN @ dt ELSE @ dt2 END as dt2 
The FROM (SELECT @ dt1:=CAST (' 2018-03-02 'as the date), @ dt2:=CAST (' 2018-06-02' as the date)) _x, 
Information_schema. Columns A 
WHERE @ dt1 & lt; @ dt2 
) A

CodePudding user response:

reference 1st floor ZJCXC response:

 SELECT dt1, dt2 FROM (
SELECT 
@ dt1 as dt1, 
@ dt1:=@ dt1 - INTERVAL EXTRACT (DAY FROM @ dt1) - 1 DAY + 1 MONTH INTERVAL, 
@ dt:=@ dt1 - INTERVAL 1 DAY, 
The CASE WHEN the @ dt1 & lt; @ dt2 THEN @ dt ELSE @ dt2 END as dt2 
The FROM (SELECT @ dt1:=CAST (' 2018-03-02 'as the date), @ dt2:=CAST (' 2018-06-02' as the date)) _x, 
Information_schema. Columns A 
WHERE @ dt1 & lt; @ dt2 
) A

Mysql internal is how to realize this statement?

CodePudding user response:

refer to the second floor u013882957 response:

Quote: refer to 1st floor ZJCXC response:

 SELECT dt1, dt2 FROM (
SELECT 
@ dt1 as dt1, 
@ dt1:=@ dt1 - INTERVAL EXTRACT (DAY FROM @ dt1) - 1 DAY + 1 MONTH INTERVAL, 
@ dt:=@ dt1 - INTERVAL 1 DAY, 
The CASE WHEN the @ dt1 & lt; @ dt2 THEN @ dt ELSE @ dt2 END as dt2 
The FROM (SELECT @ dt1:=CAST (' 2018-03-02 'as the date), @ dt2:=CAST (' 2018-06-02' as the date)) _x, 
Information_schema. Columns A 
WHERE @ dt1 & lt; @ dt2 
) A

Mysql internal is how to realize this statement?

Know that query line by line