4

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;
    }    
}
  • Possible duplicate: convert time from a different timezone other than local to GMT. The linked question deals with moving from a time zone other than the active users or UTC. – Daniel Ballinger Oct 22 '14 at 8:45
  • That does not work for this situation. That question asks to convert a time in the current user's TZ and translates to the same time in another TZ. I agree that that answer is correct for that question but does not apply to this question. – Mark Lindell Oct 22 '14 at 13:28
  • The answer goes via UTC using Timezone. I.e. Convert from Time zone A to UTC and then from UTC to Time zone B. – Daniel Ballinger Oct 22 '14 at 18:55
  • Yes, but the UTC is not known. Only the local time. All of the examples offered so far use the local time to initialize using a GMT method in an attempt to determine the difference between the target TZ and the current user's TZ and then adjust by that offset to determine the UTC. This does not work around DST. I will update my question above with the table of values between two different TZ with UTC to demonstrate the problem. – Mark Lindell Oct 22 '14 at 22:11
  • 4 years later. Did you manage to figure this out? – jmrjulian Jun 13 '18 at 21:35
5

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)
  • As I understand the documentation, DateTime.newInstanceGMT uses GMT as it's initialization values. I need to initialize a DateTime using local time to determine GMT. – Mark Lindell Oct 22 '14 at 13:25
  • 3
    You can work backwards to get the result you need. If you have the time in local, you can subtract the offset for that timezone to get the GMT value. For the case where you have 1:30AM on the Sunday where we are going back an hour, you will never be able to determine if you were talking about before the clock went back or after, without having a UTC time, since there will be two valid 1:30AM local times on that day. Other than that impossible case, you should be easily able to do the conversion you need. – Josh Kaplan Oct 22 '14 at 15:51
  • 1
    Telling statement is you say "you should be easily able" but that is not the case. – Mark Lindell Nov 24 '14 at 21:36
1

The following anonymous apex uses TimeZone.getOffset() to convert from the time in New York to UTC. Note that the getOffset() method is detecting the DST change from the perspective of a UTC input rather than the target timezones input. I.e. The offset will change between 5:59 am and 6 am in UTC rather than between 1:59 am and 2 am EST.

My current solution for this is to check the reverse offset once in UTC. If it differs for the original offset use the corrected UTC offset.

If I create a UTC DateTime for the 2nd of November 2014 at 2:00:00 a.m in EST then getOffset will return 4 hours. Checking the offset again from the 2nd of November 2014 at 6:00:00 a.m in UTC shows an offset of 5 hours. So we should actually use the 5 hour offset to determine the correct UTC value.

TimeZone tz = UserInfo.getTimeZone();
// Your results for this assertion will of course vary.
System.assertEquals('New Zealand Standard Time', tz.getDisplayName(), 'Proving the current user is in a completely different timezone to UTC and the source Timezone');

string customerTimeZoneSidId = 'America/New_York';

System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 0, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 20:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 1, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 21:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 2, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 22:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 3, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 23:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 4, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 0:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 5, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 1:00:00'), 'Sunday, 2 November 2014 at 1:00:00 a.m.');
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 5, 59, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 1:59:00'), 'Sunday, 2 November 2014 at 1:59:00 a.m.');

// Note, skips 6 am UTC to 7 am UTC

// http://www.timeanddate.com/worldclock/converted.html?iso=20141102T02&p1=179&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 7, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 2:00:00'), 'Sunday, 2 November 2014 at 2:00:00 a.m');

// http://www.timeanddate.com/worldclock/converted.html?iso=20141102T03&p1=179&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 8, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 3:00:00'), 'Sunday, 2 November 2014 at 3:00:00 a.m.');

public DateTime toUtc(string customerTimeZone, string timeZoneString) {
    DateTime customerDateTime = DateTime.valueofGmt(timeZoneString);

    TimeZone ctz = TimeZone.getTimeZone(customerTimeZone);

    integer offsetToUtc = ctz.getOffset(customerDateTime);

    //System.debug('GMT Offset: ' + offsetToUtc + ' (milliseconds) ');

    DateTime utcDateTime = customerDateTime.addMinutes(-1 * offsetToUtc / (1000 * 60));

    // 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);
    //DateTime revisedCustomerDateTime = utcDateTime.addMinutes(utcOffset / (1000 * 60));
    // Exercise for the reader, check what occurs with the other DST transition
    if(offsetToUtc != utcOffset) {
        System.debug(LoggingLevel.Warn, 'Revised UTC offset to ' + utcOffset);
        utcDateTime = customerDateTime.addMinutes(-1 * utcOffset / (1000 * 60));
    }

    System.debug('Converted ' + customerDateTime + ' to ' + utcDateTime);

    return utcDateTime;
}

Extra test cases you can try:

customerTimeZoneSidId = 'America/El_Salvador'; // San Salvador, El Salvador
//http://www.timeanddate.com/worldclock/converted.html?iso=20141101T18&p1=228&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 0, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 18:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 1, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 19:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 2, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 20:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 3, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 21:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 4, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 22:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 5, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-01 23:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 6, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 0:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 7, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 1:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 7, 59, 0), toUtc(customerTimeZoneSidId, '2014-11-02 1:59:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 8, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 2:00:00'));

customerTimeZoneSidId = 'Asia/Kathmandu'; // Nepal
//http://www.timeanddate.com/worldclock/converted.html?iso=20141102T1045&p1=117&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 5, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 10:45:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 6, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 11:45:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 7, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 12:45:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 8, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 13:45:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 9, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 14:45:00'));

customerTimeZoneSidId = 'America/Chicago'; // Central Standard Time
//http://www.timeanddate.com/worldclock/converted.html?iso=20141102T00&p1=64&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 5, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 0:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 6, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 1:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 8, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 2:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 9, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 3:00:00'));
System.assertEquals(DateTime.newInstanceGMT(2014, 11, 2, 10, 0, 0),  toUtc(customerTimeZoneSidId, '2014-11-02 4:00:00'))

Transition into Summer Time (losing 1 hour):

customerTimeZoneSidId = 'Europe/Lisbon';
//http://www.timeanddate.com/worldclock/converted.html?iso=20140330T02&p1=133&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 3, 29, 23, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-29 23:00:00')); 

System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 0, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-30 00:00:00')); 
System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 0, 59, 0), toUtc(customerTimeZoneSidId, '2014-03-30 00:59:00')); 

// http://www.timeanddate.com/worldclock/converted.html?iso=20140330T01&p1=133&p2=0
// The time Sunday, 30 March 2014 at 1:00:00 a.m. does not exist in Lisbon. 
// Daylight Saving Time skipped one hour.
//System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 0, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-30 01:00:00')); // Will Fail 

//http://www.timeanddate.com/worldclock/converted.html?iso=20140330T02&p1=133&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 1, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-30 02:00:00')); 
//http://www.timeanddate.com/worldclock/converted.html?iso=20140330T03&p1=133&p2=0
System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 2, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-30 03:00:00')); 
System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 3, 0, 0), toUtc(customerTimeZoneSidId, '2014-03-30 04:00:00')); 
  • I'm well aware of the other question/answer. It suffers from the same inaccuracies around DST. – Mark Lindell Oct 22 '14 at 22:27
  • It looks like you put a lot of work into that example. Thanks. Unfortunately, I can produce more sample data sets that will not work with your code. Are you aware that there are TZ with 30 and 15 minutes offsets? How would your solution work between TZ with varying offsets? – Mark Lindell Oct 23 '14 at 13:56
  • @MarkLindell I've added some more test cases based on your extended examples in the question. It handles the sample cases for Nepal just fine with the 5 hour 45 minute UTC offset. The code adds minutes from the offset, so as long as we aren't dealing with seconds we should be correct. – Daniel Ballinger Oct 23 '14 at 19:31
  • @MarkLindell Can you provide specific examples where it doesn't work? With regards to El Salvador, I checked the values against timeanddate.com they gave values that differ from your sample table. Can you double check those please. – Daniel Ballinger Oct 23 '14 at 19:34
  • The following will fail: :system.assertEquals('Greenwich Mean Time', UserInfo.getTimeZone().getDisplayName()); System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 0, 0, 0), toUtc('Europe/Lisbon', '2014-03-30 01:00:00')); // Will Fail System.assertEquals(DateTime.newInstanceGMT(2014, 3, 30, 1, 0, 0), toUtc('Europe/Lisbon', '2014-03-30 03:00:00')); – Mark Lindell Oct 24 '14 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.