Filter by date in MySQL

Question:

I need to filter by ranges of 6 in 6 months since my database has thousands of records and it asks me if I want to get all the data, the queries that I have tried so far are the following, but they do not adapt to what I am looking for from 6 in 6 months

SELECT * FROM citas WHERE YEAR(fecha) = 2012
SELECT * FROM citas WHERE DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= fecha ORDER BY fecha DESC 

Answer:

Assuming that date is of type DATETIME, you can make different approximations.

Schema (MySQL v5.7)

CREATE TABLE IF NOT EXISTS `citas` (
  `id` int(6) unsigned NOT NULL,
  `fecha` datetime,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `citas` (`id`, `fecha`, `content`) VALUES
  ('1', '2019-08-01 10:10:10', 'aaa'),
  ('2', '2019-06-01 10:10:10', 'bbbb'),
  ('3', '2019-02-01 10:10:10', 'ccc'),
  ('4', '2019-01-01 10:10:10', 'ddd');

Query # 1 ask for appointments from the last 6 months

SELECT * FROM citas c
WHERE c.fecha > NOW() - INTERVAL 6 MONTH;

| id  | fecha               | content |
| --- | ------------------- | ------- |
| 1   | 2019-08-01 10:10:10 | aaa     |
| 2   | 2019-06-01 10:10:10 | bbbb    |

Query # 2 ask for the previous 6 months, that is, between 6 months and a year

SELECT * FROM citas c
WHERE c.fecha < NOW() - INTERVAL 6 MONTH 
AND c.fecha > NOW() - INTERVAL 1 YEAR;

| id  | fecha               | content |
| --- | ------------------- | ------- |
| 3   | 2019-02-01 10:10:10 | ccc     |
| 4   | 2019-01-01 10:10:10 | ddd     |

Query # 3 ask for an interval between specific dates, in the example the first 6 months of the year

SELECT * FROM citas c
WHERE c.fecha BETWEEN '2019-01-01 00:00:00' AND '2019-07-01 00:00:00';

| id  | fecha               | content |
| --- | ------------------- | ------- |
| 2   | 2019-06-01 10:10:10 | bbbb    |
| 3   | 2019-02-01 10:10:10 | ccc     |
| 4   | 2019-01-01 10:10:10 | ddd     |

View on DB Fiddle

Scroll to Top