Handling Timezones with PHP and SQL Server 2000

Howdy fellas, this one is gonna be a doozy:

So for awhile I've been working on a product for my employer and we're close to launch. But we literally just realized we painted ourselves in a corner.

We never accounted for timezones when it comes to displaying them. We store times in datetime fields which are stored in our local (EST) timezone.

Assuming we add a field in the customers table to determine which timezone they are in, what's the best way to convert these times at the display level, and when we accept timestamps from a user (e.g., input field), convert it back to EST to be stored.

For our database abstraction architecture, we use the table class approach. I'm not sure of the fancy name for this method, but basically every table we use has a class with the database schema as PHP code like so:

This code would be for a table called L2FU_notification

<?php

class L2FU_notificationbase extends dbobject
{

    protected $EmailSentTimestamp;
    protected $hasBeenEmailed;
    protected $HasBeenViewed;
    protected $notificationGeneratedDate;
    protected $NotificationID;
    protected $notificationText;
    protected $notificationTitle;
    protected $releaseDate;
    protected $xtelelinkTarget;
    protected $xtelelinkToBeNotified;

    protected $fields = array('EmailSentTimestamp',
                        'hasBeenEmailed',
                        'HasBeenViewed',
                        'notificationGeneratedDate',
                        'NotificationID'
                        );

    protected $table = "L2FU_notification";
    protected $primaryKey = "NotificationID";


    function getEmailSentTimestamp()
    {
        return $this->EmailSentTimestamp;
    }

    function setEmailSentTimestamp($EmailSentTimestamp)
    {
        $this->changedFields[] = 'EmailSentTimestamp';
        $this->EmailSentTimestamp = $EmailSentTimestamp;

        return $this;
    }


    function gethasBeenEmailed()
    {
        return $this->hasBeenEmailed;
    }

    function sethasBeenEmailed($hasBeenEmailed)
    {
        $this->changedFields[] = 'hasBeenEmailed';
        $this->hasBeenEmailed = $hasBeenEmailed;

        return $this;
    }


    function getHasBeenViewed()
    {
        return $this->HasBeenViewed;
    }

    function setHasBeenViewed($HasBeenViewed)
    {
        $this->changedFields[] = 'HasBeenViewed';
        $this->HasBeenViewed = $HasBeenViewed;

        return $this;
    }


    function getnotificationGeneratedDate()
    {
        return $this->notificationGeneratedDate;
    }

    function setnotificationGeneratedDate($notificationGeneratedDate)
    {
        $this->changedFields[] = 'notificationGeneratedDate';
        $this->notificationGeneratedDate = $notificationGeneratedDate;

        return $this;
    }


    function getNotificationID()
    {
        return $this->NotificationID;
    }

    function setNotificationID($NotificationID)
    {
        $this->changedFields[] = 'NotificationID';
        $this->NotificationID = $NotificationID;

        return $this;
    }


}
?>

This is a class that was generated by an application I wrote that looks at a table schema and builds the getters, setters, etc for that class.

The dbobject class has the save and load function which writes/fetches data to/from the database to the row with the corresponding primary key.

As a last bit of background information, this is an example instantiation of the customer class (which extends customersbase which extends dbobject)

<?php
      $customerObj = new customer(1431); //let's say the customer ID we're working on is 1431. the constructor automatically calls load() when a primary key is passed at instantiation 
      $customerObj->setFirstName("Henry")->setLastName("Ford")->save();
?>

So that's some foundation on the architecture of the product I'm working on -- and it's not up for changing now, as we're already close to launch.

Here is the actual problem:

How do we handle the interoperability of timestamps in relation to timezones? Unfortunately, SQL Server 2开发者_运维知识库000 has no way to convert timezones at display time (then convert them back to local timezone at save-time), which would've been the easiest solution.

Here is an idea I had and actually started working on, but I wanted to get some feedback before I spend the time writing it to completion and it not actually working correctly.

In all of the base classes, I have the generator add a new property that lists all fields in the table that are type datetime in an array, like so:

protected $dateFields = array('EmailSentTimestamp',
                    'notificationGeneratedDate');

Then in my dbobject::load() function, When I'm populating all the field properties in the object as a result of the SQL query, if the field that is currently being iterated (via a foreach loop on the $fields array in that class) on is in the $dateFields array, it converts that value into the proper timezone before setting it in the property.

To invert this to maintain data integrity, in the save() function, when it's iterating on a datetime field, it'll convert it back to our local timestamp.

Here is an step-thru of how I'm visualizing it.

Record on the table has a field with a timestamp of 2011-05-20 13:45:00

We know the example user is from the UK and has a timezone offset of +5.

When the object is instantiated and load is called, it automatically converts that timestamp to 2011-05-20 18:45:00, so whenever the getter is called, it returns that string.

The user modifies the timestamp via a datetime picker on the webpage. He chooses 2011-05-20 20:00:00. It gets stored back to the object instance as this value.

When the save function is called, it takes the stored timestamp in the instance and converts it 2011-05-20 15:00:00 before writing it to the database.

Done.

This way, all places where we display time will automatically be "fixed" and when they need to change a certain timestamp value, it'll get adjusted automatically at save time.

So the root questions are

A) Is this the best way (and most efficient considering our tight deadline) to handle this B) if not, what is? C) What PHP solutions are there out there that will assist in the actual converting timestamps from one timezone to another.

I am under a strict CDA so I'm trying to avoid posting anymore code than I have to but let me know if any more information is needed.

Note for clarification: Changing how the time is stored is not possible/ideal. The same database is used by other products, including internal employee QA tools. Having it using a different system of time (no matter how appealing UTC is) could cause issues.

This is slightly off-topic, but if you're storing dates in local time you can't handle timezone conversions accurately 100% of the time: For 1 hour every year, during DST changeover, the time you have stored in your database is ambiguous - from a local time there is simply no way of deriving UTC (and no way to derive the appropriate time in another timezone that doesn't have the exact same DST changeover), because you don't know whether the event was in the first instance of that time, or the second.

So if it's still negotiable and you need to handle non-DST-equivalent timezones or might need to in future: Switch to storing as UTC.