complex date query

[otuatail]

Hi this is probably not achevable. I appear to hava a database (mysql) server located in europe which is one hour ahead of the uk. I NEED to subtract one hour from the query

 

Please can you tell me what country server is located? The query

 

select WorldCup.ID,WorldCup.Date, Home.Name AS Home, Away.Name AS Away, HomeScore, AwayScore
FROM WorldCup,venues,teams Home, teams Away
WHERE venues.VenueID = WorldCup.Venue AND Home.TeamID = WorldCup.Home AND Away.TeamID = WorldCup.Away
AND Date > DATE_ADD( CURDATE( ) , INTERVAL -1 DAY) AND Date < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY)
Order by Date ASC

 

Any help on this please at it is getting very anoying

 

Desmond.

 

 

[fenway]

If these are TIMESTAMP columns, you can use mysql's built-in timezone features.

[otuatail]

This is a datetime field

CREATE TABLE IF NOT EXISTS `WorldCup` (
  `ID` int(11) NOT NULL auto_increment,
  `Date` datetime default NULL,
  `Channel` tinyint(1) default NULL,
  `Venue` tinyint(1) default NULL,
  `Home` tinyint(1) default NULL,
  `Away` tinyint(1) default NULL,
  `Group` tinyint(1) default NULL,
  `Visible` char(1) NOT NULL default 'Y',
  `HomeScore` tinyint(1) default NULL,
  `AwayScore` tinyint(1) default NULL,
  `Round` tinyint(1) default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM PACK_KEYS=0;

 

This is not the problem. The problem is as soon as the clock strikes 23:00 in the UK. My query jumps to the next day. This indicates that the server is one hour ahead of england. i.e. France. I have tried to get to the bottom of this with the company that own the server but they are playing hardball with me. I have tried to prove this by creating a sample table

CREATE TABLE Orders(OrderId int NOT NULL,ProductName varchar(50) NOT NULL,OrderDate datetime NOT NULL DEFAULT NOW(),PRIMARY KEY (OrderId))

But NOW() is repoted to be an illigal command as per my post

Topic: NOW() dosn't work

 

Desmond

 

[fenway]

Well, you can't use expressions as default values, hence the error.

 

By why not just change the server time zone? Or, at the very least, change the session's timezone to compensate?