How to get users who do not access jira from a certain date?I jira in mysql.

Question

how to get users who do not access jira from a certain date?I jira in mysql.

before bd oracle to this query:

select a.user_name, a.updated_date, a.fecha , email_address 
from 
 (select u.user_name, u.updated_date, nvl2(l.attribute_value,to_date ('01/01/1970', 'dd/mm/yyyy')+(l.attribute_value/1000/60/60/24) ,null) fecha, email_address
 from jira.cwd_user u, jira.cwd_user_attributes l
 where u.id = l.user_id (+)
 and l.attribute_name (+) = 'login.lastLoginMillis'
 and u.active = 1) a
where (a.fecha is null or a.fecha < '31/12/2015')
and a.updated_date <'31/12/2015

Peter Geshev · Answer

Please accept the answer then, so that it is easier for others who may run into this problem later

Alberto_Garcia · Answer

Thank you for your help, it works perfect!!!!

Peter Geshev · Answer

Try the following query

SELECT u.user_name, u.email_address
FROM cwd_user u
LEFT JOIN cwd_user_attributes l ON l.user_id = u.id AND l.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1 AND (
	l.id IS NULL OR (
		TIMESTAMPDIFF(MICROSECOND, now(), '31/12/2015') > (l.attribute_value) * 1000
		OR 
		TIMESTAMPDIFF(SECOND, u.updated_date, '31/12/2015') < 0
	)
)