How to find out the terms which are not being used for some duration I have vocabulary and it contains more terms, so I want to implement purge logic on it. i.e. I want to delete all the terms which are not used for some duration say 1 week.

Example

term1 was associated to node1 on Oct 1st
term2 was associated to node1 on Sep 30
term1 was associated to node2 on Oct 5th

So now term 2 was not being used for last one week, so now I need to delete that term alone.

I have tried following query,

SELECT ti.tid, td.name, ti.nid
FROM taxonomy_index ti
JOIN taxonomy_term_data td ON ( td.tid = ti.tid ) 
WHERE td.vid =48
AND ti.created &lt; UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 2 WEEK ) ) 


However this query gives me both term1 and term2
Actually the reason for that is that the ti.created column is "The Unix timestamp when the node was created."  It does not track the time when the tag was applied to the node, rather the time the node was created.

So that will always have the same value for each row that describes the tag associated with any one node.  Look at this screenshot of my taxonomy_index table that displays three different tags I just applied to my node at different times:



From what I can tell, if you wanted that functionality, you'd have to store that timestamp (when a node was actually tagged) in your custom module yourself and you'd have to query that value.

Anyway, hope this helps clarify the issues a little.  :-)