Q:

String handling in MySQL - calculate days since date

I have a table that looks like this: 
table
---------------------------------------
id        last_update
---------------------------------------
1         2020-02-22T00:32:04.254975Z
2         2020-02-22T02:09:27.057131Z
3         2020-02-22T01:38:48.739303Z
4         2020-02-21T06:19:17.832257Z
5         2020-02-14T03:10:02.551126Z
6         2020-02-21T23:17:01.907037Z

id is INT and last_update is VARCHAR (because the format is not supported as a DATE TIME type in MySQL).
What I want to do is to create a new column called "days_since_last_update" that calculates the number of days between today's date and the "last_updated" date.
How can this be done in a MySQL query?
Thanks, x

A:

You should be storing your timestamps in a proper datetime column.  That being said, we can try working around the text dates using STR_TO_DATE.  I would actually suggest not adding a new column, since your ask is just for derived data.  Instead, generate this column when you query:
SELECT
    id,
    last_update,
    DATEDIFF(STR_TO_DATE(last_update, '%Y-%m-%dT%H:%i:%s'),
             (SELECT STR_TO_DATE(t2.last_update, '%Y-%m-%dT%H:%i:%s')
              FROM yourTable t2
              WHERE t2.id < t1.id ORDER BY t2.id DESC)) AS days_since_last_update
FROM yourTable t1
ORDER BY t1.id;

This would place the first days since last update value as NULL, as there is no earlier update recorded for the table.  Though, if you want a default value, that can easily be included.
If you instead want the difference in days between the last_update column and today's date, then use:
SELECT
    id,
    last_update,
    DATEDIFF(CURDATE(),
             STR_TO_DATE(last_update, '%Y-%m-%dT%H:%i:%s')) AS days_since_last_update
FROM yourTable
ORDER BY id;

