Features of working with time in different time zones

    Due to the fact that several questions and decisions on working with time have accumulated, I decided to make a small review.


    Work with various types of data in databases


    MYSQL

    In mysql, there are several standard data types for representing time; we will look at TIMESTAMPand DATETIME.
    The documentation says that a conversion policy is applied to some types of data, and not to some.
    In practice, everything is much more interesting. Let's look at a few examples:
    Create a table:
    create table xxxDate(ts TIMESTAMP NOT NULL, dt DATETIME NOT NULL);

    We’ll set the current zone for Moscow (in Moscow recently there’s no daylight saving time, and UTC + 4):
    set time_zone='Europe/Moscow';

    Create two records with summer and winter time, respectively:
    insert into xxxDate values('2012-06-10 15:08:05', '2012-06-10 15:08:05');
    insert into xxxDate values('2012-12-10 15:08:05', '2012-12-10 15:08:05');

    Let's see what a selection of these dates from the database shows:
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 15:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 15:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339326485 |
    |         1355137685 |         1355137685 |
    +--------------------+--------------------+

    We see that in both columns the values ​​are the same, this is because the function UNIX_TIMESTAMPconsiders the value of the argument in the current zone and converts it to UTC. Obviously, the same values ​​are equally converted to the same value Mon, 10 Dec 2012 11:08:05 UTC.
    Now we are moving to London!
    set time_zone='Europe/London';
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 12:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 11:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+

    There is nothing surprising, according to the documentation , TIMESTAMPbefore being inserted into the database it is converted to UTC, therefore, after we changed the current zone, the database gives us the value of this time in the current zone. Type values ​​have DATETIMEnot changed.
    Now let us consider in more detail the operation of the algorithm for Moscow. The values ​​for ts were converted to UTC when pasting, and when fetching, they were converted to values ​​in accordance with the current zone (as for London) for 15 hours, and when UNIX_TIMESTAMP was selected, they simply displayed as they were stored in the database.
    Now the expected result for London:
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339337285 | // 14h (dt)
    |         1355137685 |         1355152085 | // 15h (dt)
    +--------------------+--------------------+

    The ts values ​​have not changed, and the dt values ​​are considered as values ​​in the current zone, therefore, summer time (first record) 1339337285 = Sun, 10 Jun 2012 14:08:05 GMTand winter time (lower record) 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT.
    Just in case, check the behavior for UTC.
    set time_zone='UTC';
    select * from xxxDate;
    +---------------------+---------------------+
    | ts                  | dt                  |
    +---------------------+---------------------+
    | 2012-06-10 11:08:05 | 2012-06-10 15:08:05 |
    | 2012-12-10 11:08:05 | 2012-12-10 15:08:05 |
    +---------------------+---------------------+
    select UNIX_TIMESTAMP(ts), UNIX_TIMESTAMP(dt) from xxxDate;
    +--------------------+--------------------+
    | UNIX_TIMESTAMP(ts) | UNIX_TIMESTAMP(dt) |
    +--------------------+--------------------+
    |         1339326485 |         1339340885 | // 15h (dt)
    |         1355137685 |         1355152085 | // 15h (dt)
    +--------------------+--------------------+
    

    Everything according to the previous description, the ts values ​​have not changed, the dt values ​​are considered in the current zone, therefore they also do not change ( 1339340885 = Sun, 10 Jun 2012 15:08:05 GMT; 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT).
    Conclusion:
    • When working with DATETIMEand moving a server (incorrect time zone setting during data insertion or import) with loss of information about the time of changing the server / connection time zone, you will lose information about the actual time of events. For example, we created records at 15:00 Moscow time (we imported data into the database from backup), then set up our server on UTC and did not notice that before that the time zone was Moscow. As a result, instead of 11 hours UTC, both of our orders are now made 4 hours later - at 15 hours, and could have been another day. Therefore, in my opinion, you need to work with TIMESTAMP.
    • Also, in order to avoid unnecessary problems when debugging on the server, it is better to have a UTC zone and work with data in UTC, and on the client side, display in the zone in which the client wants.
    • Also a good example at the end of the feedbee article .
    • To avoid problems with leap second, it is also worth working with unix epochs in UTC (see the section on Leap second).


    SQLite3

    Consider the situation with sqlite3. According to the documentation in sqlite there is no data type for saving time, but there are functions for working with time stored as text, a floating-point number, and as an integer. In general, these ideas are fundamentally no different. We can assume that in sqlite the current time zone is not used unless you use the localtime and utc modifiers. For example, regardless of the system settings, CURRENT_TIMESTAMPit matters in UTC.
    $ date
    Mon Dec 10 22:05:50 MSK 2012
    $ sqlite3
    sqlite> select CURRENT_TIMESTAMP;
    2012-12-10 18:06:05
    sqlite> select datetime(CURRENT_TIMESTAMP, 'localtime');
    2012-12-10 22:06:35
    

    Therefore, convert your data in your program to utc and use unix epochs so as not to look for errors when parsing strings.
    Functions for debugging:
    select strftime('%s', CURRENT_TIMESTAMP);
    1355162582
    select datetime(1355152085, 'unixepoch');
    2012-12-10 15:08:05
    

    How the user sees the time


    If you work with the datetime type and do not convert it, then users will get confused in time. For example, if two users live in different time zones, then, seeing the same time line without specifying the zone, they will think about different times. In order not to repeat, here is a link with an example.

    C-functions for working with time


    Firstly, it is very useful to familiarize yourself with the description of working with time in glibc . We will consider several examples regarding the results of several functions in different time zones. It turns out that even the documentation says that struct tm (hereinafter referred to as broken-down time) is usually used only for display to users (because of clarity), i.e. it is better to use other more appropriate data types in your program.
    Let's look at a few examples:
    Function: struct tm * localtime_r(const time_t *time, struct tm *resultp)

    Converts simple time to broken-down time, expressed relative to the user zone.
     time_t t = 1339326485; // 2012-06-10 11:08:05 (UTC)
     struct tm bdt;
     localtime_r (&t, &bdt);
     cout << bdt.tm_hour  << endl;
     cout << bdt.tm_isdst << endl;
     cout << bdt.tm_zone  << endl;

    zone in the systemUTC Europe / MoscowEurope / London
    hour outputelevenfifteen12
    isdst output001
    zone outputUTCMskBst

     time_t t = 1355137685; // 2012-12-10 11:08:05 (UTC)

    zone in the systemUTC Europe / MoscowEurope / London
    hour outputelevenfifteeneleven
    isdst output000
    zone outputUTCMskGMT

    Function: struct tm * gmtime_r(const time_t *time, struct tm *resultp)

    Returns the value for the UTC zone, regardless of the user zone.
     time_t t = 1339326485; // 2012-06-10 11:08:05 (UTC)
     struct tm bdt;
     gmtime_r (&t, &bdt);
     cout << bdt.tm_hour  << endl;
     cout << bdt.tm_isdst << endl;
     cout << bdt.tm_zone  << endl;

    zone in the systemUTC Europe / MoscowEurope / London
    hour outputeleveneleveneleven
    isdst output000
    zone outputGMTGMTGMT

     time_t t = 1355137685; // 2012-12-10 11:08:05 (UTC)

    zone in the systemUTC Europe / MoscowEurope / London
    hour outputeleveneleveneleven
    isdst output000
    zone outputGMTGMTGMT

    Function: time_t mktime(struct tm *brokentime)

    (synonymous with timelocal , but rare)
    Converts broken-down time to simple time.
    Attention: sets the current zone for the argument.
    The tm_zone field is not considered as an argument, it is considered that the time is set in the current time zone and the time is returned in UTC.
     struct tm bdt;
     bdt.tm_sec  =  5; // 05 sec
     bdt.tm_min  =  8; // 08 min
     bdt.tm_hour = 11; // 11 h
     bdt.tm_mday = 10; // 10
     bdt.tm_mon  =  5; // 6th mon - Jun
     bdt.tm_year = 112;// 2012 - 1900
     bdt.tm_wday =  0; // ignored
     bdt.tm_yday =  0; // ignored
     bdt.tm_isdst=  0;
     bdt.tm_gmtoff= 0;
     bdt.tm_zone = "UTC";
     time_t t = mktime(&bdt);
     cout << t << endl;
     cout << bdt.tm_hour   << endl;
     cout << bdt.tm_isdst  << endl;
     cout << bdt.tm_gmtoff << endl;
     cout << bdt.tm_zone   << endl;

    zone in the systemUTC Europe / MoscowEurope / London
    pin t1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)1339312085 (Sun, 10 Jun 2012 07:08:05 GMT)1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)
    hour outputeleveneleven12
    isdst output001
    gmtoff output014400 (4 * 60 * 60)3600 (1 * 60 * 60)
    zone outputUTCMskBst

    Please note that the tm_hour and tm_isdst fields have changed for London, this is part of the process of normalizing the fields of the broken-down time structure.
    now for
    bdt.tm_mon  = 11; // 11th mon - Dec

    zone in the systemUTC Europe / MoscowEurope / London
    pin t1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)1355123285 (Mon, 10 Dec 2012 07:08:05 GMT)1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)
    hour outputeleveneleveneleven
    isdst output000
    gmtoff output014400 (4 * 60 * 60)0
    zone outputUTCMskGMT

    Function: time_t timegm(struct tm *brokentime)

    It works in UTC.
    zone in the systemUTC Europe / MoscowEurope / London
    pin t1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)1339326485 (Sun, 10 Jun 2012 11:08:05 GMT)
    hour outputeleveneleveneleven
    isdst output000
    gmtoff output000
    zone outputGMTGMTGMT
    now for
    bdt.tm_mon  = 11; // 11th mon - Dec

    zone in the systemUTC Europe / MoscowEurope / London
    pin t1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)1355137685 (Mon, 10 Dec 2012 11:08:05 GMT)
    hour outputeleveneleveneleven
    isdst output000
    gmtoff output000
    zone outputGMTGMTGMT

    Conclusion:
    If you want to display the time to the user in your program on the user computer, then use the functions timelocal/localtime, if you are working on the server, then use the functions timegm/gmtime. Also, set the UTC zone on the server, in case one of your colleagues or in a third-party library uses * local * functions. Even on the user's computer, store and work over time in UTC, so if he changes his zone, all dates will remain correct.

    Note


    Setting time zones in linux

    Consider only deb-based distributions and a couple of iron methods for setting up the time zone.
    • Method one (works on deb-based distributions):
      Run a command in the terminal and follow the instructions (“UTC” is in the “Etc” section):
      sudo dpkg-reconfigure tzdata
    • The second way (it works, probably everywhere):
      Run the command in the terminal:
      sudo ln -sf /usr/share/zoneinfo/UTC /etc/localtime 

      and just in case, edit /etc/timezone(if there is one)
    • Method three:
      Set the TZ environment variable to the desired zone, for example:
      export TZ=Europe/London

    LEAP SECOND

    This is generally a separate topic, so there are no examples in this article regarding leap second. You can check for yourself how certain functions work, as well as how different databases behave, here are mysql examples .
    • UTC includes leap second
    • Очень важное замечание:
      POSIX требует, чтобы time_t отсчитанное от 00:00:00 on January 1, 1970, UTC не включало leap seconds, но на практике иногда включает. Так же в зависимости от поддержки leap second по-разному работает функция difftime. Будьте внимательны.
    • Юлианский день так же не включает в себя leap second.
    • В mysql вы не увидите leap second, т.е. вместо 60 или 61 секунд(ы) всегда будет 59 (ссылка). Но при этом, все поддерживается и корректно работает, если вы имеете дело с unix epochs в UTC.
    • Общая рекомендация по sqlite: храните дату в виде целого числа (integer), в который уже включены leap seconds (как в mysql). Тогда вы всегда будете знать точное время.

    И еще

    • If the value of the time_zone (mysql) variable is SYSTEM, then the system zone (which was configured in the system at the time the server was started) is selected as the current zone.
    • http://www.onlineconversion.com/unix_time.htm site for converting unix time to normal time
    • GMT - can be considered an outdated concept, therefore, the article mainly uses UTC.

    Also popular now: