Group by Day of Month-CodePudding

Say I have a table logins with just the bigint id ,a date_login of type datetime, and fk_user of type bigint. I need to select the first and last logins for each day of the month in the specified year.

I was guessing something like:

select *theDayOfTheMonth*,min(date_login), max(date_login)
from logins
where year(date_login) = *theYearInput* and
and fk_user = *theKeySpecified* and
month(date_login) = *theMonthInput*
group by *theDayOfTheMonth*

but I don't know how to group by that day of month. How can I do this?

CodePudding user response：

You were close. It will look something like this:

SELECT DATE(date_login) as dayOfMonth, min(date_login), max(date_login)
FROM logins
WHERE year(date_login) = @theYearInput
    and fk_user = @theKeySpecified
    and month(date_login) = @theMonthInput
GROUP BY DATE(date_login)

Alternatively you might use the Day() function instead, which should still work because the query is limited to a single specific month via conditions in the WHERE clause:

SELECT DAY(date_login) as dayOfMonth, min(date_login), max(date_login)
FROM logins
WHERE year(date_login) = @theYearInput
    and fk_user = @theKeySpecified
    and month(date_login) = @theMonthInput
GROUP BY DAY(date_login)

Functionally, given these conditions the only difference between them is the format of the first column in the output. However, if you later need to expand across larger date ranges the first version will handle that better.

CodePudding user response：

The function that you are looking for is DayOfMonth()

create table logins(
fk_user bigint,
date_login datetime);
insert into logins values
(1,'2022-01-01 09:00:00'),(1,'2022-01-01 18:00:00'),(1,'2022-01-02 08:00:00'),(1,'2022-01-02 16:00:00')

select 
  DayOfMonth(date_login) day,
  min(date_login) first_login, 
  max(date_login) last_login
from logins
where year(date_login) = 2022 
and fk_user = 1 and
month(date_login) = 1
group by DayOfMonth(date_login);

day | first_login         | last_login         
--: | :------------------ | :------------------
  1 | 2022-01-01 09:00:00 | 2022-01-01 18:00:00
  2 | 2022-01-02 08:00:00 | 2022-01-02 16:00:00

db<>fiddle here