sqlmysql

MySQL column that will check if a date column is past 30 days

Not sure if this is possible...

I have a table with a date column called last_sent_date.

I want to add an additional column called something like "sent_last_30_days".

This column will look at the last_sent_date column and check if the date is within the last 30 days. If it is, then mark it with a 1. If not, then 0.

So the results would look something like this:

| email  | last_sent_date | sent_last_30_days |
----------------------------------------------
| email1 | 2024-07-10     |         0         |
----------------------------------------------
| email2 | 2024-08-22     |         1         |
----------------------------------------------
| email3 | 2024-09-01     |         1         | 
----------------------------------------------
| email4 | 2024-06-05     |         0         |
----------------------------------------------

I know that this can be done in Excel, but since I'm working with 35 million records, Excel is a little bit out of the question.

Can this be done in MySQL? How would that function look like?

Solution

  • Use a view:

    CREATE VIEW viewname
SELECT *, (last_sent_date >= CURRENT_DATE() - INTERVAL 30 DAY) AS sent_last_30_days
FROM tablename

    You can't use a generated column in the table because they're not allowed to use non-deterministic functions like CURRENT_DATE().