[SalesForce] How to determine UTC for a date/time represented in a time zone other than the current user’s time zone

How do you determine the UTC of a civil date/time represented in a time zone other than the current user?

I know this occurs automatically when using the DateTime.newInstance() method but it's always from the TZ of the current user.

Note: The UTC must be exact and not an approximation. In another words, it must take the historical
civil TZ changes into account (Olson TZ database)

All answers so far don't address the issue so perhaps a concrete example will add clarity:

Consider the following table of date/times:

+----------------+------------------+------------+---------------------+------------+
|      UTC       | America/New_York | UTC offset | America/Chicago     | UTC offset |
+----------------+------------------+------------+---------------------+------------+
| 11/2/2014 0:00 | 11/1/2014 20:00  |       -240 | 11/1/2014 19:00     |       -300 |
| 11/2/2014 1:00 | 11/1/2014 21:00  |       -240 | 11/1/2014 20:00     |       -300 |
| 11/2/2014 2:00 | 11/1/2014 22:00  |       -240 | 11/1/2014 21:00     |       -300 |
| 11/2/2014 3:00 | 11/1/2014 23:00  |       -240 | 11/1/2014 22:00     |       -300 |
| 11/2/2014 4:00 | 11/2/2014 0:00   |       -240 | 11/1/2014 23:00     |       -300 |
| 11/2/2014 5:00 | 11/2/2014 1:00   |       -240 | 11/2/2014 0:00      |       -300 |
| 11/2/2014 6:00 | 11/2/2014 1:00   |       -300 | 11/2/2014 1:00      |       -300 |
| 11/2/2014 7:00 | 11/2/2014 2:00   |       -300 | 11/2/2014 1:00      |       -360 |
| 11/2/2014 8:00 | 11/2/2014 3:00   |       -300 | 11/2/2014 2:00      |       -360 |
+----------------+------------------+------------+---------------------+------------+

If the current user is set to America/El_Salvador' TZ, how would I determine the values in the UTC column given that all I have is the values from the 'America/New_York' column.

Any code solution must also work for TZ where the offset is not exactly one hour such as:

+----------------+-----------------------+--------+------------------+--------+
|      utc       | America/Chicago       | offset | Asia/Kathmandu   | offset |
+----------------+-----------------------+--------+------------------+--------+
| 11/2/2014 5:00 | 11/2/2014 0:00        |   -300 | 11/2/2014 10:45  |    345 |
| 11/2/2014 6:00 | 11/2/2014 1:00        |   -300 | 11/2/2014 11:45  |    345 |
| 11/2/2014 7:00 | 11/2/2014 1:00        |   -360 | 11/2/2014 12:45  |    345 |
| 11/2/2014 8:00 | 11/2/2014 2:00        |   -360 | 11/2/2014 13:45  |    345 |
| 11/2/2014 9:00 | 11/2/2014 3:00        |   -360 | 11/2/2014 14:45  |    345 |
+----------------+-----------------------+--------+------------------+--------+

Another edge case:

+----------------+---------------------+--------+----------------+--------+
|      utc       | Greenwich Mean Time | offset | Europe/Lisbon  | offset |
+----------------+---------------------+--------+----------------+--------+
| 3/30/2014 0:00 | 3/30/2014 0:00      |      0 | 3/30/2014 1:00 |     60 |
| 3/30/2014 1:00 | 3/30/2014 2:00      |     60 | 3/30/2014 3:00 |    120 |
| 3/30/2014 2:00 | 3/30/2014 3:00      |     60 | 3/30/2014 4:00 |    120 |
+----------------+---------------------+--------+----------------+--------+

Here are some unit tests that performs a cross check with existing salesforce functions to demonstrate how the proposed solution to calculate the offset does not behave correctly during fallback. Any proposed solution must match the salesforce behavior.

@IsTest
public  with sharing class TimezoneTests {

    static testmethod void testCrossCheckForwardAllPass() {
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 8, 18, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 8, 19, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 8, 20, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 8, 22, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 8, 23, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 9, 0, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 9, 1, 0, 0));
        crossCheckDateTime('America/New_York', 'America/Chicago', DateTime.newInstanceGmt(2014, 3, 9, 3, 0, 0));
    }

    static testmethod void testCrossCheckFallBackAdjacentTimezoneFailure() {
        crossCheckDateTime('America/Indiana/Indianapolis', 'America/Chicago', DateTime.newInstanceGmt(2014, 11, 2, 6, 0, 0));
    }

    static testmethod void testCrossCheckFallBackNotOneHourDST() {        
        crossCheckDateTime('Australia/Lord_Howe', 'America/New_York', DateTime.newInstanceGmt(2014, 4, 5, 14, 59, 0));
    }

    private static void crossCheckDateTime(string customerTz, string userTz, Datetime customerDateTime )
    {
        system.debug('=========================');
        // Checks that the behavior of internal salesforce local date/time GMT calculation matches the proposed solution
        DateTime sfDateTime;
        String targetDateTime;
        String customerDateTimeDisplay;

        User u = new User(Id = UserInfo.getUserId(), TimeZoneSidKey = customerTz); 
        update u;

        System.runAs(u) { 
            sfDateTime = customerDateTime; 
            targetDateTime = customerDateTime.format('yyyy-MM-dd H:mm:ss');  
            system.debug(customerTz + ' = ' + targetDateTime + ' UTC=' + sfDateTime.formatGmt('yyyy-MM-dd H:mm:ss'));
        }

        u.TimeZoneSidKey = userTz;
        update u;

        System.runAs(u) { 
            System.assertEquals(sfDateTime, toUtc(customerTz, targetDateTime), 'Failed for ' + targetDateTime
                + ', Customer=' + customerTz + ', User=' + userTz); 
        }
    }

    private static DateTime toUtc(string customerTimeZone, string timeZoneString) {
        system.debug('customerTimeZone=' + customerTimeZone + ', timeZoneString=' + timeZoneString);
        DateTime customerDateTime = DateTime.valueofGmt(timeZoneString);
        TimeZone ctz = TimeZone.getTimeZone(customerTimeZone);
        integer offsetToUtc = ctz.getOffset(customerDateTime);
        DateTime utcDateTime = customerDateTime.addMinutes(-1 * offsetToUtc / (1000 * 60));
        system.debug('UTC: ' + utcDateTime);
        // Reverse check as getOffset will be working against UTC. We can't create an instance in the customers time zone.
        // May need to use the revised UTC offset once we can actaully work from UTC.
        integer utcOffset = ctz.getOffset(utcDateTime);
        system.debug('UTC OFFSET=' + utcOffset);
        //DateTime revisedCustomerDateTime = utcDateTime.addMinutes(utcOffset / (1000 * 60));
        // Exercise for the reader, check what occurs with the other DST transition
        if(offsetToUtc != utcOffset) {            
            utcDateTime = customerDateTime.addMinutes(-1 * utcOffset / (1000 * 60));
            system.debug('ADJUST TO: ' + utcDateTime);
        }
        return utcDateTime;
    }    
}

Best Answer

The Timezone class does exactly what you are looking for. As far as I know, it covers the entire tz database, and I know that it covers some historical changes in the database, as shown below.

Timezone tz = Timezone.getTimeZone('America/New_York');

//before the 2007 shift of DST into November
DateTime dtpre = DateTime.newInstanceGMT(2000, 11, 1, 0, 0, 0);
system.debug(tz.getOffset(dtpre));   //-18000000 (= -5 hours = EST)

//after the 2007 shift of DST into November
DateTime dtpost = DateTime.newInstanceGMT(2012, 11, 1, 0, 0, 0);
system.debug(tz.getOffset(dtpost));   //-14400000 (= -4 hours = EDT)

It also gets the exact time at which DST starts or ends.

Timezone tz = Timezone.getTimeZone('America/New_York');

DateTime dtpre = DateTime.newInstanceGMT(2014, 11, 2, 5, 59, 59);  //1:59:59AM local
system.debug(tz.getOffset(dtpre));   //-14400000 (= -4 hours = still on DST)

DateTime dtpost = DateTime.newInstanceGMT(2014, 11, 2, 6, 0, 0); //2:00:00AM local
system.debug(tz.getOffset(dtpost));  //-18000000 (= -5 hours = back one hour)
Related Topic