How to use an SQL query to display entries from last 45 days

Question

I'm using the following SQL query to populate a filter form for a jobs site. The query displays all locations used in the jobs listed on the site.

I'm only displaying jobs up to 45 days old on the site BUT the query is showing locations for ALL jobs, even those over 45 days old.

Does anyone know how I could amend this query to only show job locations from jobs that are 45 days old or less?

{exp:query sql='
    SELECT DISTINCT field_id_19 
    AS job_location 
    FROM exp_channel_data 
    WHERE field_id_19 <> "" 
    ORDER BY job_location
'}
    <option value="{job_location}" {if super_search_job_location == "{job_location}"}selected="selected"{/if}>{job_location}</option>
{/exp:query}

Thanks in advance,

Tom

Answer 1

You can try below query to display entries of last 45 Days.

SELECT DISTINCT field_id_19 AS HomeTitle FROM exp_channel_data t1 INNER JOIN exp_channel_titles t2 ON t1.entry_id = t2.entry_id WHERE t1.field_id_19 <> "" AND ((UNIX_TIMESTAMP() - t2.entry_date) < 3888000)