I am struggling to get the correct definition of "today" and "yesterday" while using CONVERT_TZ() in MySql. My dates are stored in UTC, and I need to query the dates coming out in MST timezone, so something like this produces the correct start time of the day:
select DATE_FORMAT(convert_tz(utc_timestamp(),'+00:00','-07:00'), '%m/%d/%Y 00:00:00')
However, when I put it into a query, it doesn't seem to work.
This query correctly produces the last 24 hours, but not "today" (i.e. the time from midnight to now).
SELECT * FROM tablename
WHERE CONVERT_TZ(insertdate,'+00:00','-07:00') >= convert_tz(DATE_SUB(utc_timestamp(), INTERVAL 1 DAY),'+00:00','-07:00')
Then in similar form, this produces a query that is the 24 hours before 24 hours ago, but isn't "yesterday" (i.e. yesterday from 00:00:00 to 23:59:59 of yesterday's date in MST timezone).
select * from tablename
AND CONVERT_TZ(insertdate,'+00:00','-07:00') >= convert_tz(DATE_SUB(utc_timestamp(), INTERVAL 2 DAY),'+00:00','-07:00')
AND CONVERT_TZ(insertdate,'+00:00','-07:00') <= convert_tz(DATE_SUB(utc_timestamp(), INTERVAL 1 DAY),'+00:00','-07:00')
You need to format your date using date_format function and set the time as "00:00:00" query for today is SELECT * FROM tablename WHERE CONVERT_TZ(insertdate,'+00:00','-07:00') >= date_format(convert_tz(utc_timestamp(),'+00:00','-07:00'), '%y-%m-%d 00:00:00');
Yesterday: SELECT * FROM tablename WHERE CONVERT_TZ(insertdate,'+00:00','-07:00') between date_format(convert_tz(date_sub(utc_timestamp(), interval 1 day),'+00:00','-07:00'), '%y-%m-%d 00:00:00') and date_format(convert_tz(date_sub(utc_timestamp(), interval 1 day),'+00:00','-07:00'), '%y-%m-%d 23:59:59');