Geekly Articles each Day
📜 ⬆️ ⬇️

Features of working with time in different time zones

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


Work with different types of data in databases


MYSQL

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

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

Create two entries 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 sample 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 UNIX_TIMESTAMP function considers the value of the argument in the current zone and converts it to UTC. It is obvious that the same values ​​will be converted equally into 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 , TIMESTAMP , before being inserted into the database is converted to UTC, so after we changed the current zone, the database gives us the value of this time in the current zone. DATETIME values ​​have not changed.
Now let's take a closer look at the work of the algorithm for Moscow. Values ​​for ts when converted were converted to UTC, and when sampled, they were converted to values ​​according to the current zone (as for London) 15 hours, and when UNIX_TIMESTAMP was selected, they were 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 daylight saving time (first record) is 1339337285 = Sun, 10 Jun 2012 14:08:05 GMT , and winter time (lower record) is 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 is as previously described, ts values ​​have not changed, dt values ​​are considered in the current zone, therefore they do not change 1339340885 = Sun, 10 Jun 2012 15:08:05 GMT; 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT ( 1339340885 = Sun, 10 Jun 2012 15:08:05 GMT; 1355152085 = Mon, 10 Dec 2012 15:08:05 GMT ) .
Conclusion:


SQLite3

Consider the situation with sqlite3. According to the documentation in sqlite there is no data type to save time, but there are functions for working with time, saved as text, a floating point number and as an integer. In general, these views are fundamentally no different. We can assume that the current time zone is not used in sqlite if you do not use the localtime and utc modifiers. For example, regardless of the system settings, CURRENT_TIMESTAMP has a value 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 utc in your program and use unix epochs in order not to look for errors when parsing lines.
Debugging features:
 select strftime('%s', CURRENT_TIMESTAMP); 1355162582 select datetime(1355152085, 'unixepoch'); 2012-12-10 15:08:05

How user sees time


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

C-functions to work with time


First, it is very useful to get acquainted with the description of work with time in glibc . We will consider several examples concerning the results of the work of several functions in different time zones. It turns out that even the documentation states that the struct tm (hereinafter the broken-down time) is usually used only for displaying to users (because of clarity), i.e. in your program it is better to use other more suitable data types.
Consider 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 systemUTCEurope / MoscowEurope / London
output houreleven1512
isdst output00one
zone outputUTCMSKBst

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

zone in the systemUTCEurope / MoscowEurope / London
output houreleven15eleven
isdst output000
zone outputUTCMSKGMT

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

Returns the value for a 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 systemUTCEurope / MoscowEurope / London
output houreleveneleveneleven
isdst output000
zone outputGMTGMTGMT

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

zone in the systemUTCEurope / MoscowEurope / London
output houreleveneleveneleven
isdst output000
zone outputGMTGMTGMT

 Function: time_t mktime(struct tm *brokentime)

(synonymous with timelocal , but rarely)
Converts broken-down time to simple time.
Note: sets the current zone for the argument.
The tm_zone field is not considered 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 systemUTCEurope / MoscowEurope / London
t output1339326485 (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)
output houreleveneleven12
isdst output00one
gmtoff output014,400 (4 * 60 * 60)3600 (1 * 60 * 60)
zone outputUTCMSKBst

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

zone in the systemUTCEurope / MoscowEurope / London
t output1355137685 (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)
output houreleveneleveneleven
isdst output000
gmtoff output014,400 (4 * 60 * 60)0
zone outputUTCMSKGMT

 Function: time_t timegm(struct tm *brokentime)

Works in UTC.
zone in the systemUTCEurope / MoscowEurope / London
t output1339326485 (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)
output houreleveneleveneleven
isdst output000
gmtoff output000
zone outputGMTGMTGMT
now for
 bdt.tm_mon = 11; // 11th mon - Dec

zone in the systemUTCEurope / MoscowEurope / London
t output1355137685 (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)
output houreleveneleveneleven
isdst output000
gmtoff output000
zone outputGMTGMTGMT

Conclusion:
If you want to display the time to the user in your program on the user's computer, then use the timelocal/localtime functions, if you are working on the server, then use the timegm/gmtime . Also, install a UTC zone on the server, in case any of your colleagues or 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 a time zone.

LEAP SECOND

In general, this is a separate topic, so in this article there are no examples relating to leap second. You can check for yourself how certain functions work, as well as how different databases behave, here are examples of mysql .

And further

Source: https://habr.com/ru/post/162341/

More articles:


All Articles