how to add time in mysql query

Question

i am using this query

select date_time_posting from table where FROM_UNIXTIME(date_time_posting,'%d-%m-%Y')='$c_day_date'

where $c_day_date is having dates in dd-mm-yy format like 25-07-2009

My server is in different time zone from mine.Total difference is of about 6 hours i.e. my local timezone is 6 hours ahead of my server timezone.

This query runs in the midnight at around 01:00 according to my timezone but at that time my server remains in the previous day date.

Then at that time this query doesnt give me desired result.Why this problem is persisting when im doing everything with the already stored data.Why this time difference is affecting my query and how can i rectify it????

Answer 1

You can set per client timezone by sending a query like:

$sql = "SET time_zone = timezone;"

check the manual at MySQL to try to rectify this problem.