How to get rows of last minute of every month?

Question

I have a table that one of its column, named 'date', is of type DATETIME. I need to select all the rows that their date is the last minute of a month, for example:

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

How can I achieve this in one query?

Answer 1

You could use LAST_DAY to get the last day of the month and DATE_FORMAT to get the time to compare.

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 correct row we need to get the last day of the month AND last minute of the day

LAST_DAY will help use to get the last day of the month for the given date-time. And DATE_FORMAT will help to get the date. Now, we will combine them together, to get the last date of the given date-time.

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

Above will return 29, if last day of the month is 29-02-2018.

Now, we need to check, if given date-time has last minute of the day ? We can again make use of DATE_FORMAT to extract time from the given date-time. Here, we will only concentrate on hour and minute (as per OP question). So, it should be

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

Above will return 23:59, if given date-time is 29-02-2018 23:59:00.

Answer 2

You can use LAST_DAY to get the last day of each month:

SELECT LAST_DAY(mydate)

returns:

2031-12-31
2030-11-30

Then use the STR_TO_DATE in order to get the last minute of the last day of each month:

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

returns:

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

You can now use last_min to compare with your actual datetime value.

If you want to get records whose datetime falls within the last minute interval, then you can additionally use DATE_ADD to get the next minute of the above datetime values:

SELECT DATE_ADD(last_min, 
                INTERVAL 1 MINUTE) AS next_min

returns:

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

Using the above expressions you can build a predicate that checks for dates within the desired interval.

Demo here