MySQL自日期起当前和上个月的第一天和最后一天(无时间戳） - MySQL first day and last day of current and previous month from date (no timestamp)

MySQL first day and last day of current and previous month from date (no timestamp)

我希望下面的查询能让你知道我在寻找什么-

SELECT SUM(t1.hours) AS totalhours FROM
(
    SELECT (time_to_sec(timediff(time_out, time_in)) / 3600) AS hours FROM bb_work_log 
    WHERE user_id = 6 AND (working_date BETWEEN '2014-04-01' AND '2014-04-31')
) AS t1

在我的查询中，您可以看到我在这里手动给出的working_date。但是，我不想手动操作。我想动态地选择本月的第一天和最后一天。

上月第一天

select last_day(curdate() - interval 2 month) + interval 1 day

上个月的最后一天

select last_day(curdate() - interval 1 month)

当月第一天

select last_day(curdate() - interval 1 month) + interval 1 day

本月最后一天

select last_day(curdate())

您可以使用LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY，它将从现在起减去一个月，通过在上月的LAST_DAY中添加1天，将获得当月的第一天

SELECT SUM(t1.hours) AS totalhours FROM
(
    SELECT (time_to_sec(timediff(time_out, time_in)) / 3600) AS hours FROM bb_work_log 
    WHERE user_id = 6 
    AND (working_date BETWEEN LAST_DAY(NOW() - INTERVAL 1 MONTH) 
    AND LAST_DAY(NOW()))
) AS t1

LAST_DAY（NOW（）-间隔1个月）这将给你的最后一天上月

每月第一天/最后一天Fiddle演示

您可以通过以下方式实现它----

/* Current month*/
SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-1 DAY),CONCAT(LAST_DAY(NOW()),' 23:59:59');
SELECT LAST_DAY(CURDATE()) - INTERVAL DAY(LAST_DAY(CURDATE()))-1 DAY ,CONCAT(LAST_DAY(NOW()),' 23:59:59');
/* previous month*/
SELECT  DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m-01 00:00:00'),DATE_FORMAT(LAST_DAY(CURDATE()-INTERVAL 1 MONTH),'%Y-%m-%d 23:59:59');

-- first day of previous month
set @start_date = date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01');
-- last day of previous month
set @end_date = date_format(NOW() , '%Y-%m-01') - INTERVAL 1 day;
select @start_date ,@end_date ;
--  first day of current month
set @start_date = date_format(NOW(), '%Y-%m-01');
-- last dat of current month
set @end_date = date_format(NOW() + INTERVAL 1 MONTH, '%Y-%m-01') - INTERVAL 1 day;
select @start_date ,@end_date ;