Question 114394 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to group by date, month and week date in sql?

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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");

  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search