PostgreSQL's date_trunc in mySQL

The extract function seems useful and the date function I have found solves some of my problems, but is there any way to replicate PostgreSQL's date_trunc?

Indeed, EXTRACT looks like it's going to be the closest match for this specific case.

Your original code in PG:

WHERE date_trunc('month', QUERY_DATE) BETWEEN 
    date_trunc('month', now()) - INTERVAL '4 MONTH' AND 
    date_trunc('month', now() - INTERVAL '1 WEEK')

Using EXTRACT:

WHERE EXTRACT(YEAR_MONTH FROM QUERY_DATE)
      BETWEEN
          EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 4 MONTH)
      AND
          EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 WEEK)

While it should be functionally identical, this is actually mangling the dates into a YYYYMM string before doing the comparison.

Another option would be using DATE_FORMAT to rebuild the date string and force it to the beginning of the month:

WHERE DATE_FORMAT(QUERY_DATE, '%Y-%m-01')
      BETWEEN
          DATE_FORMAT(NOW() - INTERVAL 4 MONTH, '%Y-%m-01')
      AND
          DATE_FORMAT(NOW() - INTERVAL 1 WEEK, '%Y-%m-01')

Also, be aware that MySQL is really poor at dealing with date ranges, even when the field is indexed. You're probably going to end up with a full table scan if you aren't careful.

late to the party, but...

there is a way to get truncated date given you know the interval. For example, if the interval is MONTH, you could get today's date (now()) truncated to the month using the following:

select date_add('1900-01-01', interval TIMESTAMPDIFF(MONTH, '1900-01-01', now()) MONTH);

Given the above, one could create a function to take care of the other intervals as well:

DELIMITER //
create function date_trunc(vInterval varchar(7), vDate timestamp)
returns timestamp
begin
    declare toReturn timestamp;

    if vInterval = 'year' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(YEAR, '1900-01-01', vDate) YEAR);
    elseif vInterval = 'quarter' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(QUARTER, '1900-01-01', vDate) QUARTER);
    elseif vInterval = 'month' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MONTH, '1900-01-01', vDate) MONTH);
    elseif vInterval = 'week' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(WEEK, '1900-01-01', vDate) WEEK);
    elseif vInterval = 'day' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(DAY, '1900-01-01', vDate) DAY);
    elseif vInterval = 'hour' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(HOUR, '1900-01-01', vDate) HOUR);
    elseif vInterval = 'minute' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MINUTE, '1900-01-01', vDate) MINUTE);
    END IF;

    return toReturn;
end//
DELIMITER ;

Use it like so:

select date_trunc('quarter', now())