Search code examples
phpmysqldatetimetimezoneconvert-tz

Change timezone offset for YEARWEEK using COVERT_TZ function, php, mysql

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)";
Solution

  • 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.