How to group by date, month and week date in sql?-CodePudding

I have a column createdAt which is datetype timestamp , I want to count how many records I have in that period. I try to group them by "DATE" However, I get an error: No function matches the given name and argument types. You might need to add explicit type casts.

Here is my query:

select "createdAt" as period, count(id) as count from members where ("createdAt" between '2022-08-01' and '2022-08-10') group by date("createdAt");

How do I create these tables in SQL?

__________________
count | period
__________________
11    | 2022-08-08

__________________
count | period
__________________
11    | Monday

CodePudding user response：

The DATEFIRST setting depends on SQL Server's language version. The default value for U.S. English is 7 (i.e. Sunday).

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

CodePudding user response：

In the end I resolved with extract. Here is the code:

select extract(week from "createdAt") as period, count(id) as n_people from members where '20220801' <= "createdAt" and "createdAt" <= '20220810' group by extract(week from "createdAt");