Mysql to get the current date with time 23:59:59

Question

If I execute

select current_date;

This will give the current date.

What if I have to get the current date with 23:59:59 , So that it will be end of the day.

In SQL Server I used

Select CAST(CONVERT(VARCHAR(10), GETDATE(), 110) + ' 23:59:59' AS   
DATETIME;

Answer 1

One method is to add seconds:

select date_add(CURDATE(), interval 24*60*60 - 1 second)

Another method is addtime():

select addtime(CURDATE(), '23:59:59')

Answer 2

Adding interval of 1 day - 1 second] would only work if the datetime / timestamp in question has time set to 00:00:00 (that's the case of CURDATE() and CURRENT_DATE).

However, if you need to work with datetime / timestamp set to, for example, 2021-08-05 02:00:00, the aforementioned method would yield 2021-08-06 01:59:59.

In such case, you may use DATE_FORMAT:

SELECT expire_at, DATE_FORMAT(expire_at, "%Y-%m-%d 23:59:59")
FROM coupons;

(tested on MySQL 8.0.18)