get data from mysql table as per IST zone

Question

I am using GoDaddy webserver of shared hosting.I have a mysql database in which there are 2 columns which contains datatype date.. I am using following sql queries. From query1 I am getting Expired records & from query2 getting ongoing records. Problem is because webserver uses it's local time so as per indian time zone I am not getting accurate records. Is there any way to fix problem in query.

Query1

SELECT * FROM offers WHERE startDate <= CURRENT_DATE AND endDate <= CURRENT_DATE

Query2

SELECT * FROM offers WHERE startDate <= CURRENT_DATE AND endDate >= CURRENT_DATE

Answer 1

If your values in database are stored in IST then below should work.

Query 1

SELECT * FROM offers WHERE DATE_ADD(startDate,INTERVAL -330 MINUTE) <= CURRENT_DATE AND DATE_ADD(endDate,INTERVAL -330 MINUTE) <= CURRENT_DATE

Query2

SELECT * FROM offers WHERE DATE_ADD(startDate,INTERVAL -330 MINUTE) <= CURRENT_DATE AND DATE_ADD(endDate,INTERVAL -330 MINUTE) >= CURRENT_DATE

Answer 2

You need to convert the time zones. I assume MySql is using UTC time by default.

SELECT * FROM offers WHERE CONVERT_TZ(startDate,'UTC','Asia/Kolkata') <= CURRENT_DATE AND CONVERT_TZ(endDate,'UTC','Asia/Kolkata') <= CURRENT_DATE;

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

You need to check time zone tables.

SELECT * FROM mysql.time_zone;
SELECT * FROM mysql.time_zone_name;

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Mysql uses these tables to convert th time zones. If above tables are empty. You have to fill it.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo

more info here http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html