How can I create a self-incrementing ID per day in MySQL?

Question

I have a table

bills
( id INT NOT NULL AUTOINCREMENT PRIMARY KEY
, createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
, idDay INT NULL
);

I want the 1st record of the idDay field of each day to be 1 and from there continue the incremental, example:

|    id    |    createdAt   | idDay |
|----------|----------------|-------|
| 1        | 2021-01-10     |   1   |
| 2        | 2021-01-10     |   2   |
| 3        | 2021-01-11     |   1   |
| 4        | 2021-01-11     |   2   |
| 5        | 2021-01-11     |   3   |
| 6        | 2021-01-12     |   1   |
| 7        | 2021-01-13     |   1   |
| 8        | 2021-01-13     |   2   |

It's necessary the idDay field? or can i do this in the select?. I think I can do this with a procedure but how?.

Thanks for help. 😁

Answer 1

You can use the row_number() window function available since MySQL 8.

SELECT id,
       createdat,
       row_number() OVER (PARTITION BY date(createdat)
                          ORDER BY id) idday
       FROM bill;

(Or ORDER BY createdat, if that defines the order, not the id.)

But since window functions are calculated after a WHERE clause is applied, the number might be different for a record if previous records for a day are filtered. It's not clear from your question if this is a problem or not. If it is a problem, you can use the query in a derived table or create a view with it and work on that.

Yet another option is a correlated subquery counting the "older" records.

SELECT b1.id,
       b1.createdat,
       (SELECT count(*) + 1
               FROM bill b2
               WHERE b2.createdat >= date(b1.cratedat)
                     AND b2.createdat < date_add(date(b1.createdat), INTERVAL 1 DAY)) 
                     AND b2.id < b1.id) idday
       FROM bill b1;

(If createdat defines the order, change b2.createdat < date_add(date(b1.createdat), INTERVAL 1 DAY)) to b2.createdat <= b1.createdat.)
That would also work in lower MySQL versions and you can add a WHERE clause (to the outer query) without changing the numbers.