How to get the last minute lines of each month?

Question

I have a table in which one of its columns named 'date' is of type DATETIME. I need to select all the lines in which their date is the last minute of the month, for example:

31-12-17 23:59:00
30-11-17 23:59:00 

How can I achieve this in a single request?

Answer 1

You can use to get the last day of the month and to get time for comparison. LAST_DAY DATE_FORMAT

SELECT * FROM <table_name> 
WHERE DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")=DATE_FORMAT(<date_time_col>,"%d") 
AND DATE_FORMAT(<date_time_col>,"%H:%i")='23:59';

---

Detailed explanation:

So basically, to get the right line, we need to get the last day of the month, and the last minute of the day

LAST_DAY . DATE_FORMAT . , .

DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")

29, 29-02-2018.

, ? DATE_FORMAT . ( ). ,

DATE_FORMAT(<date_time_col>,"%H:%i")

23:59, - 29-02-2018 23:59:00.

Answer 2

LAST_DAY, :

SELECT LAST_DAY(mydate)

:

2031-12-31
2030-11-30

STR_TO_DATE, :

SELECT STR_TO_DATE(CONCAT(LAST_DAY(mydate), 
                   ' ', 
                   '23:59:00'),
                   '%Y-%m-%d %H:%i:%s') AS last_min

:

last_min
--------------------
2031-12-31T23:59:00Z
2030-11-30T23:59:00Z
2030-11-30T23:59:00Z

last_min .

, datetime , DATE_ADD, datetime:

SELECT DATE_ADD(last_min, 
                INTERVAL 1 MINUTE) AS next_min

:

next_min
---------------------
2032-01-01T00:00:00Z
2030-12-01T00:00:00Z
2030-12-01T00:00:00Z

, , .

Answer 3

SELECT * FROM table WHERE DATE(date) = LAST_DAY(date) AND HOUR(date) = 23 AND MINUTE(date) = 59;

- , .

Answer 4

:

SELECT * FROM mytable WHERE mydate IN
(
'2017-01-31 23:59:00',
'2017-03-31 23:59:00',
'2017-04-30 23:59:00',
'2017-05-31 23:59:00',
'2017-06-30 23:59:00',
'2017-07-31 23:59:00',
'2017-08-31 23:59:00',
'2017-09-30 23:59:00',
'2017-10-31 23:59:00',
'2017-11-30 23:59:00',
'2017-12-31 23:59:00'
)
OR mydate = '2017-03-01 00:00:00'- INTERVAL 1 MINUTE;

Answer 5

, , datetime :

SELECT *
FROM testdata
WHERE CAST(`date` AS DATE) = LAST_DAY(`date`)
AND EXTRACT(HOUR_MINUTE FROM `date`) = 2359