Mysql vs Timezone [solved]

[paul]

I've got a query .. .see below

it does some date stuff ...

 

it gets the date from the server (timezone support)

the server this runs on is in a different timezone to me ...

 

who's the SQL wizard here?

SELECT DISTINCT(ID_EVENT),
ID_TOPIC,title,startDate,endDate,members.ID_MEMBER, memberName
FROM calendar
JOIN members USING(ID_MEMBER)
WHERE
(`startDate` >= CURDATE()
AND
`endDate` <= ADDDATE('2008-04-26', interval 7 day))
OR
(`startDate` < CURDATE()
AND
`endDate` >= ADDDATE('2008-04-26', interval 7 day))
ORDER BY startDate
ASC

[paul]

you lot a are too slow :)

 

SELECT DISTINCT(ID_EVENT),
ID_TOPIC,title,startDate,endDate,members.ID_MEMBER, memberName
FROM calendar
JOIN members USING(ID_MEMBER)
WHERE
(`startDate` >= CONVERT_TZ(CURDATE(),'UTC','NZ')
AND
`endDate` <= CONVERT_TZ(ADDDATE('2008-04-26', interval 7 day),'UTC','NZ'))
OR
(`startDate` < CONVERT_TZ(CURDATE(),'UTC','NZ')
AND
`endDate` >= CONVERT_TZ(ADDDATE('2008-04-26', interval 7 day),'UTC','NZ'))
ORDER BY startDate
ASC

[Steve Scrimpshire]

Glad we could help.

[SilverSurfer60]

I was just about to give you the answer.

[tyme]

If I had seen this in the half hour between you first in second posts...i coulda helped.

 

:D