Selecting date based on user and server time zones

[djones]

I'm inserting/displaying date/time based on this flow:

 

INPUT

User input -> convert to server time -> insert date/time into table

 

OUTPUT

select date/time from table -> convert to user's date/time - > display date/time to user

 

The above part works great until I need the events for a full day.

 

My problem is when I need to get events based on a day, month, year and not the time. I need to get the full days events.

 

If a user picks Jan, 28th 2010 at 00:00:00 time it will insert into the table for the 27th 23:00:00 (server time).

 

When I query the database or events on the 28th I do not have the time. Only the month,day,year. So  its looking for events on the 28th when it was inserted as 27(server time). How can I get around this?

 

[djones]

I got lucky and tried to use the time offset from each timezone in seconds then use DATE_ADD/DATE_SUB based on the integer and corrected the time in the SQL statement to relfect the user's timezone. And it worked. Shew.... timezones area a headache. Went ok up until this point.