UNIX Time functions MySQL/MariaDB vs the rest

UNIX Time functions MySQL/MariaDB vs the rest

by Benjamin Ellis -
Number of replies: 2
Picture of Particularly helpful Moodlers

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.

Average of ratings: -
In reply to Benjamin Ellis

Re: UNIX Time functions MySQL/MariaDB vs the rest

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

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.

Average of ratings: Useful (1)