0

Okay here is what I am trying to do: 

 $timezone1 = '+00:00';
 $timezone2 = '+08:00';

WHERE DATE(CONVERT_TZ(from_unixtime(comment_date), '$timezone1', '$timezone2')) =  DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)";

That line gives me the total count of yesterdays submissions in a table, by transforming the output into my correct timezone.

Now I want to do the same with the total count of this weeks submissions: 

 WHERE YEARWEEK(from_unixtime(comment_date), 1) =  YEARWEEK(CURRENT_DATE, 1)";

that gives me the total count of submissions for this week, yet with the wrong time offset. How do I get $timezone1 and $timezone2 into this line? I tried several combinations, but nothing seems to work, such as:

 WHERE (CONVERT_TZ(YEARWEEK(from_unixtime(comment_date), 1), '$timezone1', '$timezone2')) =  YEARWEEK(CURRENT_DATE, 1)";
fedorqui
  • 275,237
  • 103
  • 548
  • 598
Georgi Grancharov
  • 183
  • 1
  • 2
  • 8

1 Answers1

1

I'm not a PHP or MySQL developer, but it sounds like you ought to just be changing DATE in your original code with YEARWEEK

WHERE YEARWEEK(CONVERT_TZ(from_unixtime(comment_date), '$timezone1', '$timezone2')) 
    =  YEARWEEK(CURRENT_DATE, 1)";

If that's not working, please give more information about the way in which it's not working.

Also note that you're not really providing time zones - you're providing offsets. That means your code will give inconsistent results based on daylight saving time. This may or may not be a problem, depending on your requirements.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks for your help - It gives me an error message **Incorrect parameter count in the call to native function 'CONVERT_TZ'** The offset problem during daylight saving time doesn't concern me, since I can switch it manually. What I am trying is just apply the same logic I used in the "yesterday" output to a "this week" output. I think the line you provided is correct, there should be just a minor error. I am trying to find it now. – Georgi Grancharov Nov 03 '12 at 09:34
  • @GeorgiGrancharov: Oops, I've got some brackets wrong. Will fix those. It's not clear what the `1` argument in from_unixtime is for in your YEARWEEK version. – Jon Skeet Nov 03 '12 at 09:35
  • It comes from an answer I received in this thread here: [http://stackoverflow.com/questions/13192170/mysql-date-show-results-today-yesterday-week#comment17957641_13192170] - I'll check it now – Georgi Grancharov Nov 03 '12 at 09:38
  • Your answer works, I have no way to check it but as for now it displays this weeks current submissions. Thanks a lot, @Jon Skeet – Georgi Grancharov Nov 03 '12 at 09:39