I have a table with day/time values that are split into date and an int fields respectively rather than a single datetime. Each row has a unique combo of date and hour, so there are 24 rows per each day.
My challenge is selecting data relevant for users in a different timezone when I need to take these timezone adjustments into account in the where statement. For example, if the user's two hours ahead of the server time, the where statement needs to reflect it:
...where concat(theDate,' ',theHour) > convert_tz(concat('2012-01-01',' ','00:00:00'), '-8:00', '-6:00')
..for, say, PST to central time. But obviously I can't use convert_tz or a value with which it was calculated in a where clause.
I'd like to use convert_tz rather than clumsy DATE_ADD or whatever. What can be done?
Maybe I'm wrong and you can have convert_tz in a where clause. Here's the core issue:
select *, convert_tz(concat(theDay,' ',theHour), '-8:00', '-8:00') 'dayTZ'
from stats
where convert_tz(concat(theDay,' ',theHour), '-8:00', '-8:00') > '2011-01-25 05:00:00';
The above's still returning hour values earlier than 5.
Try adding str_to_date:
select *, convert_tz(sconcat(theDay,' ',theHour), '-8:00', '-8:00') 'dayTZ'
from stats
where str_to_date(convert_tz(concat(theDay,' ',theHour), '-8:00', '-8:00'), '%Y-%m-%d %H:%i:%s') > str_to_date('2011-01-25 05:00:00', '%Y-%m-%d %H:%i:%s');