Changing Timezones

[rklockner]

I have inheritted an enormous code base that was never written to consider the timezone a user is in.  I have been tasked with adding in a timezone field for users to select, then adjust the time everywhere it is used.

 

There are literally hundreds (if not thousands) of places where the time is displayed. 

 

Hopefully there is a possibility to adjust this without having to adjust every MySQL query.

 

Ideally,

1 - The query calls the data on the server

2 - Either PHP or MySQL recognizes it as a date and adjusts it based on the user's timezone automatically.

 

NOTE: I am familiar with how the user can select their timezone in PHP (date_default_timezone_set)

 

Not Ideal (as this would take weeks to implement),

 

function scriptToChangeTZ($datetime){

    ...adjust to new timezone code

}

 

$datetime = /*date from MySQL query */

 

$new_datetime = scriptToChangeTZ($datetime);

[fenway]

If you used TIMESTAMP, you'd have do absolutely nothing.

[rklockner]

It is 50/50, and very random.

 

Would I be able to go through the database and just convert DATETIME to TIMESTAMP?

[fenway]

As long as you're in the valid TIMESTAMP range, then yes -- it's not useful for things like birthdays, but then again, those aren't subject to timezones, and shouldn't have been DATETIMEs anyway.

[rklockner]

From the responses I am getting, I think I need to:

1 - create a function to adjust the timezone

2 - start implementing the function across the software

3 - gradually implement the new function on all date/time strings

4 - add the ability for user's to change their timezone.

 

Based on everything I am hearing, this is the way to go. I cannot convert all DATETIMEs to TIMESTAMPs, so this wouldn't be a complete fix.

[fenway]

The DATETIMEs you can't covert you don't need to convert -- you must be confused.