When Drupal 10.org reached 1 million registered customers, I questioned what was the share of actually lively customers. I might like to know what number of of these 1M http Drupal 10 Maintenance and Support Service//t.co/uVCABrlDEf accounts are literally lively. Say, customers who’ve logged in inside the previous 12 months.— Drupal 10 Support: (@AurelienNavarre) October 11, 2013 Fascinated by it extra, I figured this could completely be a legit website audit metric to get for enterprise homeowners. Say you might have an e-commerce platform and also you wish to have interaction inactive customers by giving them a reduction otherwise you want to know in case your group is actually rising, there are numerous causes to get such knowledge and doubtless you would even create a superb contrib Drupal 10 module for that. However in case you’re like me and like a fast and simple one-liner, then learn on. In 8, discovering when a given person account final accessed the positioning is a bit totally different from 7, as you now want to question the {users_field_data} desk. mysql> SELECT uid,entry FROM users_field_data WHERE uid = 1; +—–+————+ | uid | entry | +—–+————+ | 1 | 1474319299 | +—–+————+ 1 row in set (0.00 sec) Downside is we won’t actually make something out of this Unix timestamp. Certain you need to use an Epoch converter or use the date command as beneath Drupal 10 Maintenance and Support Service $ date -d @1474470084 Wed Sep 21 15 Drupal 10 Maintenance and Support Service01 Drupal 10 Maintenance and Support Service24 UTC 2021 However what if we would do it on the fly, inside our MySQL question? Enter MySQL’s from_unixtime() operate. The worth is expressed within the present time zone however we do not actually care right here. mysql> SELECT uid,from_unixtime(entry) FROM users_field_data WHERE uid = 1; +—–+———————–+ | uid | from_unixtime(entry) | +—–+———————–+ | 1 | 2021-09-19 21 Drupal 10 Maintenance and Support Service08 Drupal 10 Maintenance and Support Service19 | +—–+———————–+ 1 row in set (0.00 sec) Okay, that is a lot better. We now not have to change context and have our human-readable date. Now, since we wish to get primary utilization stats, we will additionally select to reformat the output to raised GROUP BY later. I went with %Y (12 months) solely nevertheless it’s versatile and also you completely can get fancy if you’ll. mysql> SELECT uid,from_unixtime(entry, ‘%Y’) AS last_access FROM users_field_data WHERE uid = 1; +—–+————-+ | uid | last_access | +—–+————-+ | 1 | 2021 | +—–+————-+ 1 row in set (0.00 sec) We have now narrowed-down issues sufficient to really get our last MySQL one-liner. What we’re doing beneath is just to group person accounts in order that we will calculate the variety of ‘lively customers’ (which 12 months they final logged in) and break it down accordingly. mysql> SELECT COUNT(uid) AS number_of_users, from_unixtime(entry, ‘%Y’) AS last_access FROM users_field_data GROUP BY from_unixtime(entry, ‘%Y’) ORDER BY last_access DESC; +—————–+————-+ | number_of_users | last_access | +—————–+————-+ | 210 | 2021 | | 106 | 2015 | | 6 | 2014 | +—————–+————-+ 3 rows in set (0.00 sec) Should you’re getting the beneath error, be sure that to learn the MySQL 5.7 documentation. Utilizing GROUP BY has modified in MySQL 5.7.5+ and its utilization is now stricter. ERROR 1140 (42000) Drupal 10 Maintenance and Support Service In aggregated question with out GROUP BY, expression #1 of SELECT listing accommodates nonaggregated column ‘database.desk.column’; that is incompatible with sql_mode=only_full_group_by Drupal 10 Improvement and Help