So I have a healthy table of hourly stats where the day and time are split rather than kept as a singular datetime:
+-------+-------------+------------+----------+
| cakes | pies | day | hour |
+-------+-------------+------------+----------+
| 1 | 28 | 2012-02-21 | 20 |
| 0 | 14 | 2012-02-21 | 21 |
| 1 | 15 | 2012-02-21 | 22 |
| 1 | 11 | 2012-02-21 | 23 |
| 0 | 7 | 2012-02-22 | 0 |
| 0 | 9 | 2012-02-22 | 1 |
| 0 | 5 | 2012-02-22 | 2 |
| 0 | 8 | 2012-02-22 | 3 |
| 1 | 11 | 2012-02-22 | 4 |
| 0 | 11 | 2012-02-22 | 5 |
| 0 | 12 | 2012-02-22 | 6 |
| 1 | 19 | 2012-02-22 | 7 |
| 0 | 26 | 2012-02-22 | 8 |
| 0 | 20 | 2012-02-22 | 9 |
| 0 | 24 | 2012-02-22 | 10 |
| 2 | 26 | 2012-02-22 | 11 |
| 1 | 22 | 2012-02-22 | 12 |
| 1 | 24 | 2012-02-22 | 13 |
| 1 | 32 | 2012-02-22 | 14 |
| 0 | 25 | 2012-02-22 | 15 |
| 2 | 20 | 2012-02-22 | 16 |
| 0 | 24 | 2012-02-22 | 17 |
| 1 | 24 | 2012-02-22 | 18 |
| 0 | 15 | 2012-02-22 | 19 |
+-------+-------------+------------+----------+
I want to do timezone-sensitive selects from this table so people around the world can know about my cake or pie quotas at any hour.
Here's what my query would be without timezone conversion:
select cakes, pies, day, hour,
str_to_date(concat(day,' ',hour,':00'), '%Y-%m-%d %H:%i:%s') 'this' from stats where
str_to_date(concat(day,' ',hour,':00'), '%Y-%m-%d %H:%i:%s') between
str_to_date('2012-02-21 20:00:00', '%Y-%m-%d %H:%i:%s') and
str_to_date('2012-02-21 23:00:00', '%Y-%m-%d %H:%i:%s');
..which returns the first four rows of the dataset above:
+-------+-------------+------------+----------+---------------------+
| cakes | pies | day | hour | this |
+-------+-------------+------------+----------+---------------------+
| 1 | 28 | 2012-02-21 | 20 | 2012-02-21 20:00:00 |
| 0 | 14 | 2012-02-21 | 21 | 2012-02-21 21:00:00 |
| 1 | 15 | 2012-02-21 | 22 | 2012-02-21 22:00:00 |
| 1 | 11 | 2012-02-21 | 23 | 2012-02-21 23:00:00 |
+-------+-------------+------------+----------+---------------------+
So far so good. Now I need to make this timezone-sensitive. Say my server's in California and someone in New Zealand during daylight savings is trying to access my cake and pie stats from 2012-02-21 20:00:00 through 2012-02-21 23:00:00:
select cakes, pies, day, hour,
str_to_date(convert_tz(concat(day,' ',hour,':00'), '+13:00','-8:00'), '%Y-%m-%d %H:%i:%s') 'this' from stats where
str_to_date(convert_tz(concat(day,' ',hour,':00'), '+13:00','-8:00'), '%Y-%m-%d %H:%i:%s') between
str_to_date(convert_tz('2012-02-21 20:00:00', '+13:00','-8:00') , '%Y-%m-%d %H:%i:%s') and
str_to_date(convert_tz('2012-02-21 23:00:00', '+13:00','-8:00') , '%Y-%m-%d %H:%i:%s');
But here's where it gets weird:
+-------+-------------+------------+----------+---------------------+
| cakes | pies | day | hour | this |
+-------+-------------+------------+----------+---------------------+
| 1 | 28 | 2012-02-21 | 20 | 2012-02-20 23:00:00 |
| 0 | 14 | 2012-02-21 | 21 | 2012-02-21 00:00:00 |
| 1 | 15 | 2012-02-21 | 22 | 2012-02-21 01:00:00 |
| 1 | 11 | 2012-02-21 | 23 | 2012-02-21 02:00:00 |
+-------+-------------+------------+----------+---------------------+
It SEEMS to think it's looking up timezone-adjusted values (the "this" column), but the stats returned are the exact same as those from the non-timezoned query! What's going on here?
You're converting all the dates to compare (both input dates and dates from the database) using convert_tz so no surprises you get the same data back. I think what you probably want is
SELECT cakes, pies, day, hour,
CONVERT_TZ(CONCAT(day,' ',hour,':00'), '+13:00','-8:00') AS 'this'
FROM stats
WHERE
CONVERT_TZ(CONCAT(day,' ',hour,':00'), '+13:00','-8:00')
BETWEEN
'2012-02-21 20:00:00'
AND '2012-02-21 23:00:00'
But why are you storing the date and time separately? It would make your queries far easier to store them together as a DATETIME field. Also you don't need to use STR_TO_DATE on values that are already MySQL DATETIME values.