Question 20882 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get the number of registers by month and date – Mysql

I have rows of user data. I store the createDate, which is the timestamp in milliseconds when the user registered. I want to get the total number of registrations per month. When I try to do that, I don’t get any rows returned. Here’s the query I’m using

SELECT COUNT(*) FROM users WHERE YEAR(createDate) = 2023 GROUP BY MONTH(createDate)

createDate is BIGINT and is the date in milliseconds

Tags:

2

Answers


  1. 0

    I guess your createDate column is defined as TIMESTAMP(3), to get millisecond resolution. LAST_DAY() comes in handy here.

    Try this:

    SELECT COUNT(*), LAST_DAY(createDate) month_ending
  FROM users
 WHERE createDate >= '2023-01-01' 
   AND createDate <  '2024-01-01'
 GROUP BY LAST_DAY(createDate)

    The date range test I use for getting the dates in a single year is sargable. That is, it can be accelerated by an index on createDate, where YEAR(createDate) cannot be.

    This approach generates a useful result set if you run it on a multi-year date range.

    But, if your result set is empty (has no rows), the result set from this query will be too. That might mean:

    • your table has no dates in that range, or
    • your createDate data type is something other than TIMESTAMP or DATETIME. (You didn’t show us the table definition.)
    Login or Signup to reply.
  2. 0

    It sounds like you need to convert to/from unix time:

    SELECT COUNT(*), LAST_DAY(FROM_UNIXTIME(createDate/1000)) month_ending
  FROM users
 WHERE createDate >= UNIX_TIMESTAMP('2023-01-01') * 1000
   AND createDate <  UNIX_TIMESTAMP('2024-01-01') * 1000
 GROUP BY month_ending
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search