2

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.

fedorqui
  • 275,237
  • 103
  • 548
  • 598
Alkanshel
  • 4,198
  • 1
  • 35
  • 54
  • You can use `convert_tz` in a where clause. Why do you think you can't? – Ben Lee Jan 31 '12 at 23:35
  • It's just a regular MySQL function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz – Ben Lee Jan 31 '12 at 23:36
  • In any case, if you are comparing dates, you can't just use `concat` like you did. You have to then convert it into a date time for the comparison to work: `STR_TO_DATE(CONCAT(theDate, ' ', theHour))` – Ben Lee Jan 31 '12 at 23:38
  • Ben Lee: convert_tz complains if I try to pass it an STR_TO_DATE val: "Incorrect parameter count in the call to native function 'STR_TO_DATE'". – Alkanshel Feb 01 '12 at 00:16
  • Nevermind what I wrote about STR_TO_DATE. I was wrong. That's only necessary if your strings are not in the standard MySQL date time format. – Ben Lee Feb 01 '12 at 00:50
  • The updated query you posted should work. What problem are you having with it? – Ben Lee Feb 01 '12 at 00:56
  • Whoops, the last line in my edit got chopped off, sorry. The above query still grabs entries with hours earlier than 5:00. I think convert_tz is somehow outputting a string, so it's returning everything because the comparison is between strings and meaningless.. really dunno though. Thanks for the prompt responses, btw. – Alkanshel Feb 01 '12 at 01:19
  • Okay, if you think that's the problem, go back to trying str_to_date. (It wasn't working for you before because I forgot to mention it requires a "format" argument). See my answer. – Ben Lee Feb 01 '12 at 01:23

1 Answers1

1

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');
Ben Lee
  • 52,489
  • 13
  • 125
  • 145
  • This shows unwanted hour rows too, I'm afraid.. there's no way that's not a date comparison, though. Weird. – Alkanshel Feb 01 '12 at 21:13
  • Actually, this works in unix but grabs the wrong hours in my local windows environment. This is the right answer, I don't know why my windows install of mysql is being dumb though. – Alkanshel Feb 01 '12 at 21:22
  • wait, what is the format of `concat(theDay,' ',theHour)`?. I've been assuming it is a standard `yyyy-mm-dd hh:mm:ss` but if it's something else, that entirely explains your problem. – Ben Lee Feb 01 '12 at 21:23