The Ultimate Guide to High Availability with MariaDB

Select data on previous month

Hi,

Would need some help on how to query previous month data

(eg. need to extract data for previous month.. the script will be called via cronjob on first day of current month) s Tect * fler table_name where execute_date > (current_date() - interval 1 month)

Is the aboev query ight syntax to extract for previous month?rcmhanks

Answer Answered by Anel Husakovic in this comment.

CURDATE()

SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-01-30 |
+------------+

DATE_ADD()

SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
+---------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2019-02-28                            |
+---------------------------------------+

DATE_SUB()

SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
+---------------------------------------+
| DATE_SUB(CURDATE(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2018-12-30                            |
+---------------------------------------+

DATEDIFF()

SELECT DATEDIFF(CURDATE(), DATE_SUB(CURDATE(), INTERVAL 2 MONTH));
+------------------------------------------------------------+
| DATEDIFF(CURDATE(), DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) |
+------------------------------------------------------------+
|                                                         61 |
+------------------------------------------------------------+

Generate data:

First create a table with records in interval of "n" months:
In my case it is 3 months starting from now()

CREATE TEMPORARY TABLE daterange (dt DATE); 

SET @counter := -1;
SET @nmonths := 3;
SET @to:= curdate();
SET @from := date_sub(curdate(), INTERVAL @nmonths MONTH);
set @diff := DATEDIFF(@to, @from);

Results:

SHOW FIELDS FROM daterange;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| dt    | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

SELECT @to;
+------------+
| @to        |
+------------+
| 2019-01-30 |
+------------+

SELECT @from;
+------------+
| @from      |
+------------+
| 2018-10-30 |
+------------+

SELECT @diff;
+-------+
| @diff |
+-------+
|    92 |
+-------+

Insert data into the table

DELIMITER //
WHILE (@counter < @diff) DO 
    INSERT daterange VALUES (DATE_ADD(@from, INTERVAL @counter:=@counter + 1 DAY));
END WHILE; //
DELIMITER ;

Results:

Query OK, 93 rows affected (0.024 sec)

SELECT COUNT(dt) from daterange; 
+-----------+
| count(dt) |
+-----------+
|        93 |
+-----------+

Get the first and last 5 days:


Get the first 5 days:

SELECT * FROM daterange LIMIT 5; 
+------------+
| dt         |
+------------+
| 2018-10-30 |
| 2018-10-31 |
| 2018-11-01 |
| 2018-11-02 |
| 2018-11-03 |
+------------+

Get the last 5 days from table:

SELECT * FROM daterange ORDER BY(dt) DESC LIMIT 5; 
+------------+
| dt         |
+------------+
| 2019-01-30 |
| 2019-01-29 |
| 2019-01-28 |
| 2019-01-27 |
| 2019-01-26 |
+------------+

Get the interval

Get the x [day,month] data (@x) from table,
starting from @mydate, where @mydate is 1 day before today

SET @mydate:= DATE_SUB(curdate(),INTERVAL 1 day);
SET @x:= 1;

SELECT @mydate;
+------------+
| @mydate    |
+------------+
| 2019-01-29 |
+------------+

Result for day interval:

SELECT * FROM daterange WHERE dt>=DATE_SUB(@mydate, INTERVAL @x day) AND dt<=@mydate;
+------------+
| dt         |
+------------+
| 2019-01-28 |
| 2019-01-29 |
+------------+
2 rows in set (0.001 sec)

Result for month interval:

SELECT * FROM daterange WHERE dt>=DATE_SUB(@mydate, INTERVAL @x MONTH) AND dt<=@mydate;
+------------+
| dt         |
+------------+
| 2018-12-29 |
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-04 |
| 2019-01-05 |
| 2019-01-06 |
| 2019-01-07 |
| 2019-01-08 |
| 2019-01-09 |
| 2019-01-10 |
| 2019-01-11 |
| 2019-01-12 |
| 2019-01-13 |
| 2019-01-14 |
| 2019-01-15 |
| 2019-01-16 |
| 2019-01-17 |
| 2019-01-18 |
| 2019-01-19 |
| 2019-01-20 |
| 2019-01-21 |
| 2019-01-22 |
| 2019-01-23 |
| 2019-01-24 |
| 2019-01-25 |
| 2019-01-26 |
| 2019-01-27 |
| 2019-01-28 |
| 2019-01-29 |
+------------+
32 rows in set (0.001 sec)

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.