Date timezone help

[lukep11a]

Hi, the date column of my login table is updated with the current time when a user registers, can anyone tell me how I can change the query below so that login.date is converted to GMT+0100?

 

<?php
        $query = "SELECT test_teams.team, test_teams.selectiongroup, SUM(test_team_points.points) AS total FROM test_teams, test_team_points, login, test_selections WHERE login.userid = '{$_SESSION['userid']}' AND login.userid = test_selections.userid AND test_selections.teamid = test_teams.teamid AND test_teams.teamid = test_team_points.teamid AND login.date < test_team_points.date GROUP BY test_teams.teamid ORDER BY test_teams.selectiongroup";
        $result = mysql_query($query) or die(mysql_error());
        
        while($row = mysql_fetch_assoc($result))
        {
        ?>

[ZulfadlyAshBurn]

use date_default_timezone_set();

 

 

[lukep11a]

thanks for your reply, do you know how I can apply it to my code? sorry I am still relatively new to php

[ZulfadlyAshBurn]

what the the country you are in?

[lukep11a]

i am in the UK

[ZulfadlyAshBurn]

change your code to this.

 

<?php
date_default_timezone_set('Europe/London');

$query = "SELECT test_teams.team, test_teams.selectiongroup, SUM(test_team_points.points) AS total FROM test_teams, test_team_points, login, test_selections WHERE login.userid = '{$_SESSION['userid']}' AND login.userid = test_selections.userid AND test_selections.teamid = test_teams.teamid AND test_teams.teamid = test_team_points.teamid AND login.date < test_team_points.date GROUP BY test_teams.teamid ORDER BY test_teams.selectiongroup";
        $result = mysql_query($query) or die(mysql_error());
        
        while($row = mysql_fetch_assoc($result))
        {
        ?>

[xyph]

Why wouldn't you just use MySQL to do the conversion. It's much faster.

 

 SELECT CONVERT_TZ(`date`, @@time_zone, 'destination tz' ) FROM `login` 

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

[lukep11a]

SELECT CONVERT_TZ(`date`, @@time_zone, 'destination tz' ) FROM `login`

 

would I have to redo that every time a new user registers though?

[xyph]

Redo what? That will convert any datetime from MySQLs default time zone to your destination time zone.

 

I have no idea what the rest of your script does, or what you're trying to accomplish. Your job as a programmer is to take that snippet and make it work for what you're trying to do.

[ZulfadlyAshBurn]

Lukep11a, u are supposed to use that code and implement it in your mysql query thus u dont need to redo it. Its done automatically by the php page when the user register. If u dont understand how it works then just use date_default_timezone_set();

[lukep11a]

ok thankyou all for your help