Send out email at a user's local time.

Question

I am writing a program that needs to send out an email every hour on the hour, but at a time local to the user.

Say I have 2 users in different time zones. John is in New York and Fred is in Los Angeles. The server is in Chicago. If I want to send an email at 6 PM local to each user, I'd have to send the email to John at 7 PM Server time and Fred at 4 PM Server time.

What's a good approach to this in .NET / Sql Server? I have found an xml file with all of the time zone information, so I am considering writing a script to import it into the database, then querying off of it.

Thanks

Edit: I used t4znet.dll and did all comparisons on the .NET side.

Answer 1

I'm a PHP developer so I'll share what I know from PHP. I'm sure .NET will include something similar.

In PHP you can get timezone differences for the server time - as you've suggested you'd send the emails at different times on the server.

Every time you add a user save their time offset from the server time (or their timezone in case the server timezone changes).

Then when you specify an update, have an automated task (Cron for LAMP people) that runs each hour checking to see if an email needs to be sent. Do this until there are no emails left to send.

Answer 2

You have two options:

• Store the adjusted time for the mail action into the database for each user. Then just compare server time with stored time. To avoid confusion and portability issues, I would store all times in UTC. So, send mail when SERVERUTCTIME() == storedUtcTime.
• Store the local time for each mail action into the database, then convert on-the-fly. Send mail when SERVERUTCTIME() == TOUTCTIME(storedLocalTime, userTimeZone).

You should decide what makes most sense for your application. For example if the mailing time is always the same for all users, it makes more sense to go with option (2). If the events times can change between users and even per user, it may make development and debugging easier if you choose option (1). Either way you will need to know the user's time zone.

*These function calls are obviously pseudo, since I don't know their invocations in T-SQL, but they should exist.

Answer 3

You can complement your solution with this excellent article "World Clock and the TimeZoneInformation class", I made a webservice that sent a file with information that included the local and receiver time, what I did was to modify this class so I could handle that issue and it worked perfect, exactly as I needed.

I think you could take this class and obtain from the table "Users" the time zone of them and "calculate" the appropiate time, my code went like this;

//Get correct destination time
DateTime thedate = DateTime.Now;

string destinationtimezone = null;

//Load the time zone where the file is going
TimeZoneInformation tzi = TimeZoneInformation.FromName(this.m_destinationtimezone);

//Calculate
destinationtimezone = tzi.FromUniversalTime(thedate.ToUniversalTime()).ToString();

This class has an issue in Windows Vista that crashes the "FromIndex(int index)" function but you can modify the code, instead of using the function:

    public static TimeZoneInformation FromIndex(int index)
    {
        TimeZoneInformation[] zones = EnumZones();

        for (int i = 0; i < zones.Length; ++i)
        {
            if (zones[i].Index == index)
                return zones[i];
        }

        throw new ArgumentOutOfRangeException("index", index, "Unknown time zone index");
    }

You can change it to;

    public static TimeZoneInformation FromName(string name)
    {
        TimeZoneInformation[] zones = EnumZones();

        foreach (TimeZoneInformation tzi in zones)
        {
            if (tzi.DisplayName.Equals(name))
                return tzi;
        }

        throw new ArgumentOutOfRangeException("name", name, "Unknown time zone name");
    }