I need to write a database-independent SQL query that identifies users who have not
logged in for 12 months after their last login. I can use the
MySQL/MariaDB functions such as UNIX_TIMESTAMP and FROM_UNIXTIME but
I understand the Unix timestamp functions are not supported in M$SQL
and am not sure for the other supported engines. Does anyone know of
a way to deal with this kind of issue? I certainly do not want to
examine each user record in PHP.
There is no standard way to convert to/from unix timestamps in SQL. However, you could get the timestamp for 12 months ago with PHP, and select the users who haven't logged in since without having to check every record yourself. Something like $DB->get_records_select('user', 'lastlogin < ?', [time()-31557600])
might do it? You can use the DateTime API for better accuracy.
Thanks - makes sense.