MandrivaUsers.org > Mysql vs Timezone [solved]

Full Version: Mysql vs Timezone [solved]

MandrivaUsers.org > Advanced Topics > Command Line, Kernel and Programming

paul

Apr 25 2008, 09:00 PM

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?

CODE

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

Apr 25 2008, 09:33 PM

you lot a are too slow

CODE

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

Apr 26 2008, 03:57 AM

Glad we could help.

SilverSurfer60

Apr 26 2008, 05:52 AM

I was just about to give you the answer.

tyme

Apr 26 2008, 07:36 PM

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

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.