Abstract: Take a closer look at the organization rules of the common time zone database, how time zones and daylight saving time are maintained, and how they are used in GaussDB(DWS).
This article is shared with Leapdb by Leapdb, the Universal Time Zone Database of Time Zone Knowledge you Should Know.
1. Background
Next, take a closer look at the organization rules for the common time zone database, how time zones and daylight saving time are maintained, and how they are used in GaussDB(DWS).
2. General time zone database
Local time zones and daylight saving time rules are managed independently by their respective governments, which often change them with limited notice. And their historical data and future plans are only intermittently recorded. The Universal Time Zone database attempts to organize and organize data related to this domain.
The Time zone database, often referred to as TZ, TZData or ZoneInfo, is a collection of code and data that represents the history of local time in many representative locations around the globe, updated irregularly according to regime changes to time zone boundaries and daylight saving time rules. Each entry in the database represents the time zone information for a civilian clock that has been widely accepted since 1970. The database is referenced by many projects, such as: theGNU C Library (used in GNU/Linux), Android, FreeBSD, NetBSD, OpenBSD, ChromiumOS, Cygwin, MariaDB, MINIX, MySQL, WebOS, AIX, BlackBerry 10, iOS, macOS,Microsoft Windows, OpenVMS, Oracle Database, and OracleSolaris. Like other widely used software products, GaussDB(DWS) uses the universal time zone data maintained by IANA.
The database was created by David Olson and edited and maintained by PaulEggert. It is also called an Olson database in some places. Its distinctive feature is a common naming convention for time zones designed by Paul Eggert. Each time zone is given a unique name in a “region/location” format, such as “America/New_York”. Spaces in English place names are replaced by the underscore “_”, and the conjunction “-” is used only when the English place name itself contains. Time zone databases are commonly referred to as Olson time zone databases or IANA time zone databases.
Olson’s numbers have changed, partly due to ADOlson’s impending retirement and partly due to a copyright infringement lawsuit filed against the vader (now dropped). On October 14, 2011, the Internet Corporation for Assigned Names (IANA) took over the maintenance of the time zone database. It is regularly updated to reflect changes made by political entities to time zone boundaries, UTC differentials, and daylight saving time rules. Tz updates are managed according to the BCP 175 process.
Time zone rules change frequently in some countries, and IANA releases updated time zone data and parsing source code every year. IANA provides three ways to access the time zone database:
1. www.iana.org/time-zones
2. ftp://ftp.iana.org/tz/
3. rsync://rsync.iana.org/tz/
The time zone database contains text files of the original time zone definitions for each continent and code files that parse these text files.
2.1 General time zone database source code
Related information:
Source code hosting address: The introduction of time zone database https://data.iana.org/time-zones/tz-link.html https://github.com/eggert/tz time zone database principle and use https://data.iana.org/time-zones/theory.htmlCopy the codeDownload method:
The mkdir TZDB CD TZDB wget https://www.iana.org/time-zones/repository/tzcode-latest.tar.gz # download the latest wget text parsing time zone defined code file https://www.iana.org/time-zones/repository/tzdata-latest.tar.gz # download the latest time zone defined gzip text file - the dc tzcode - latest. Tar. Gz | tar xf - Gzip - dc tzdata - latest. Tar. Gz | tar xf - # or download the code + the wget complete package of data https://www.iana.org/time-zones/repository/tzdb-latest.tar.lz # download code + data of complete time zone database lzip - dc TZDB - latest. Tar. Lz | tar xf -Copy the codeCode structure:
Time zone definition file (text file) included in the Time zone database: Africa Antarctica Asia AustralAsia Europe NorthAmerica SouthAmerica
C date.c difftime.c localtime.c strftime.c zdump.c zic.c Code file contained in the time zone database (used to parse the time zone definition file)
Installation directory structure:
The installation directory structure is as follows
CD TZDB - 2020 - a make TOPDIR = $HOME/tzdir install leap @ gaussdb: ~ >. The/sbin/tree - 3 tzdir/L | more tzdir / ├ ─ ─ etc │ └ ─ ─ Localtime # local time zone file └ ─ ─ usr ├ ─ ─ bin │ ├ ─ ─ tzselect # set the time zone of the tool │ └ ─ ─ zdump # tool in text display a time zone change history ├ ─ ─ lib │ └ ─ ─ libtz. A ├─ ├─ ├─ # ├─ ├─ # └─ Share ├─ Man ├─ zoneinfo ├─ zoneinfo-posix -> zoneinfo └─ All exercises ├─ zoneinfo #IANA time database is used by the GNU C Library (used in GNU/Linux), so the directory after install is also organized according to the Linux system directory.Copy the codeZic. C in the code is a tool that parses raw text files of time zone definitions into binary time zone files. If a software product needs to get the latest time zone data from IANA, it needs to get both the source code and the data, and generate a ZIC parser to generate time zone files for the software product to use. The parsing code for the time zone file is in localtime.c
2.2 Rules and Maintenance methods of Time Zone Raw Data
The RAW file of the time zone database is a text file organized according to certain rules. Here’s an example:
This rule is scientifically designed to record historical information about time zone and DAYLIGHT saving time changes. This universal time zone library can automatically convert historical time, so it is widely used.
Here is the definition of Moscow’s time zone
Zone NAME STDOFF RULES FORMAT [UNTIL]
Zone Europe/Moscow 2:30:17 - LMT 1880
2:30:17 - MMT 1916 Jul 3 # Moscow Mean Time
2:31:19 Russia %s 1919 Jul 1 0:00u
3:00 Russia %s 1921 Oct
3:00 Russia MSK/MSD 1922 Oct
2:00 - EET 1930 Jun 21
3:00 Russia MSK/MSD 1991 Mar 31 2:00s
2:00 Russia EE%sT 1992 Jan 19 2:00s
3:00 Russia MSK/MSD 2011 Mar 27 2:00s
4:00 - MSK 2014 Oct 26 2:00s
3:00 - MSK
Copy the codeFrom 2am on January 19, 1992 to 2am on March 27, 2011, the east Third District is adopted. From 2am on March 27, 2011 to 2am on October 26, 2014, the East fourth District is adopted after 2am on October 26, 2014
How to read the RAW file of the time zone database data.iana.org/time-zones/… VSCode’s Zoneinfo plugin allows you to view RAW files with time zone data syntactically highlighted
The basic function of the time zone database is to provide the time zone data raw files and a compiler to convert the time zone data raw files into time zone files: ZIC.
You can learn about the use of the time zone file compiler, which compiles time zone definition text files into binary time zone data files, with Man ZIC
3. Use the universal time zone database
The specific definition of each time zone name exists in a time zone file, such as /etc/localtime. The time zone files supported by the OPERATING system are stored in the /usr/share/zoneinfo/ directory. The timezone files supported by our database are stored in the share/timezone directory.
The time zone file has a unified format (from the time zone database maintained by IANA) and can be viewed using info tzfile. Fixed start structure for time zone files:
struct tzhead { char tzh_magic[4]; /* tzh_version[1]; /* tzh_version[1]; /* '\0' or '2' or '3' as of 2013, version info */ char tzh_reserved[15]; /* reserved; must be zero */ char tzh_ttisutcnt[4]; /* Coded number of trans. Time flags UTC/local flags saved in files */ char tzh_ttisstdcnt[4]; /* Coded number of trans. Time flags */ coded number of Standard /wall indicators stored in files */ char tzh_leapcnt[4]; /* Coded number of leap seconds */ char tzh_timecnt[4]; /* coded number of leap seconds */ coded number of Leap seconds */ char tzh_timecnt[4]; /* coded number of transition times */ char tzh_typecnt[4]; /* coded number of transition times */ coded number of transition times */ char tzh_typecnt[4]; /* coded number of local time types stored in files (non-zero!) */ char tzh_charcnt[4]; /* Coded number of abbr. chars number of "time zone abbreviations" saved in files */ /};Copy the codeFollowing these headers are the tZH_timecnt “standard” byte order four-byte values of type LONG, sorted in ascending order. Each value is taken as a change time(like the return of time(2)), and the system relies on these values to calculate the local time change.
This is followed by tzh_timecnt one-byte values of type unsigned char that indicate which of the multiple “local time” types described in the file is associated with change times with the same index. These values serve as indexes to the ttINFO structured array.
The ttinfo structure is defined later in the file as follows:
struct ttinfo {
long tt_gmtoff;
int tt_isdst;
unsigned int tt_abbrind;
};
The structure consists of a four-byte long value tt_gmtoff in “standard” byte order, a one-byte TT_ISdst and a one-byte TT_abbrind. In each structure, tt_gmtoff gives the time to be added to UTC in seconds,tt_isdst indicates whether TM_ISDST can be set with localtime (3), and tt_abbrind can be used as an array index of time zone abbreviations. This array follows the ttINFO structure in the file.
This gives tZH_leapcnt four byte pairs in standard byte order. The first value of each four-byte pair gives the time at which a Leap second occurs, as in the return of time(2); The second value of each four-byte pair gives the total number of Leap seconds implemented after a given time. Four byte pairs sorted in ascending order by time.
There are also tzh_ttisstdCNt standard/ Wall indicators, each holding a one-byte value; These indicators indicate whether the change time (relative to the local time type) is stated as standardTime or Wall Clock time, and whether the change time is used when a time zone file is used to process posiX-formatted time zone environment variables.
Finally, there are tzh_ttisgmtCNT UTC/local indicators, each holding a one-byte value; These indicators indicate whether change time (relative to the local time type) is stated as UTC or Local time, and whether change time is used when a time zone file is used to process posiX-formatted time zone environment variables.
If tzh_timecnt is equal to zero or the time parameter is smaller than the first time change recorded in the file, Localtime uses the first standard time ttinfo in the file, or the first ttINFO structure if there is no standard time structure.
The man Zdumpdump binary time zone data file shows how to view the time zone change history
./zdump -V Asia/Chongqing | more Asia/Chongqing Sat Dec 31 16:53:39 1927 UT = Sat Dec 31 23:59:59 1927 LMT isdst=0 gmtoff=25580 Asia/Chongqing Sat Dec 31 16:53:40 1927 UT = Sat Dec 31 23:53:40 1927 LONT isdst=0 gmtoff=25200 Asia/Chongqing Wed Apr 30 16:59:59 1980 UT = Wed Apr 30 23:59:59 1980 LONT isdst=0 gmtoff=25200 Asia/Chongqing Wed Apr 30 17:00:00 1980 UT = Thu May 1 01:00:00 1980 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat May 3 15:59:59 1986 UT = Sat May 3 23:59:59 1986 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat May 3 16:00:00 1986 UT = Sun May 4 01:00:00 1986 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 13 14:59:59 1986 UT = Sat Sep 13 23:59:59 1986 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 13 15:00:00 1986 UT = Sat Sep 13 23:00:00 1986 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 11 15:59:59 1987 UT = Sat Apr 11 23:59:59 1987 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 11 16:00:00 1987 UT = Sun Apr 12 01:00:00 1987 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 12 14:59:59 1987 UT = Sat Sep 12 23:59:59 1987 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 12 15:00:00 1987 UT = Sat Sep 12 23:00:00 1987 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 9 15:59:59 1988 UT = Sat Apr 9 23:59:59 1988 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 9 16:00:00 1988 UT = Sun Apr 10 01:00:00 1988 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 10 14:59:59 1988 UT = Sat Sep 10 23:59:59 1988 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 10 15:00:00 1988 UT = Sat Sep 10 23:00:00 1988 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 15 15:59:59 1989 UT = Sat Apr 15 23:59:59 1989 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 15 16:00:00 1989 UT = Sun Apr 16 01:00:00 1989 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 16 14:59:59 1989 UT = Sat Sep 16 23:59:59 1989 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 16 15:00:00 1989 UT = Sat Sep 16 23:00:00 1989 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 14 15:59:59 1990 UT = Sat Apr 14 23:59:59 1990 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 14 16:00:00 1990 UT = Sun Apr 15 01:00:00 1990 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 15 14:59:59 1990 UT = Sat Sep 15 23:59:59 1990 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 15 15:00:00 1990 UT = Sat Sep 15 23:00:00 1990 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 13 15:59:59 1991 UT = Sat Apr 13 23:59:59 1991 CST isdst=0 gmtoff=28800 Asia/Chongqing Sat Apr 13 16:00:00 1991 UT = Sun Apr 14 01:00:00 1991 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 14 14:59:59 1991 UT = Sat Sep 14 23:59:59 1991 CDT isdst=1 gmtoff=32400 Asia/Chongqing Sat Sep 14 15:00:00 1991 UT = Sat Sep 14 23:00:00 1991 CST isdst=0 gmtoff=28800Copy the codeSince China abolished daylight saving time in 1991, it has not changed.
4. How to use time zone data in GaussDB(DWS)
For convenience, GaussDB(DWS) internally defines the Asia/Beijing time zone for users based on the IANA syntax rules. The time zone definitions are consistent with those of PRC. Timezone /Asia/Beijing in the loaded directory
Asia/Beijing
./zdump -V Asia/Beijing Asia/Beijing Sat May 3 15:59:59 1986 UT = Sat May 3 23:59:59 1986 CST isdst=0 gmtoff=28800 Asia/Beijing Sat May 3 16:00:00 1986 UT = Sun May 4 01:00:00 1986 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 13 14:59:59 1986 UT = Sat Sep 13 23:59:59 1986 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 13 15:00:00 1986 UT = Sat Sep 13 23:00:00 1986 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 11 15:59:59 1987 UT = Sat Apr 11 23:59:59 1987 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 11 16:00:00 1987 UT = Sun Apr 12 01:00:00 1987 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 12 14:59:59 1987 UT = Sat Sep 12 23:59:59 1987 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 12 15:00:00 1987 UT = Sat Sep 12 23:00:00 1987 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 9 15:59:59 1988 UT = Sat Apr 9 23:59:59 1988 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 9 16:00:00 1988 UT = Sun Apr 10 01:00:00 1988 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 10 14:59:59 1988 UT = Sat Sep 10 23:59:59 1988 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 10 15:00:00 1988 UT = Sat Sep 10 23:00:00 1988 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 15 15:59:59 1989 UT = Sat Apr 15 23:59:59 1989 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 15 16:00:00 1989 UT = Sun Apr 16 01:00:00 1989 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 16 14:59:59 1989 UT = Sat Sep 16 23:59:59 1989 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 16 15:00:00 1989 UT = Sat Sep 16 23:00:00 1989 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 14 15:59:59 1990 UT = Sat Apr 14 23:59:59 1990 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 14 16:00:00 1990 UT = Sun Apr 15 01:00:00 1990 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 15 14:59:59 1990 UT = Sat Sep 15 23:59:59 1990 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 15 15:00:00 1990 UT = Sat Sep 15 23:00:00 1990 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 13 15:59:59 1991 UT = Sat Apr 13 23:59:59 1991 CST isdst=0 gmtoff=28800 Asia/Beijing Sat Apr 13 16:00:00 1991 UT = Sun Apr 14 01:00:00 1991 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 14 14:59:59 1991 UT = Sat Sep 14 23:59:59 1991 CDT isdst=1 gmtoff=32400 Asia/Beijing Sat Sep 14 15:00:00 1991 UT = Sat Sep 14 23:00:00 1991 CST isdst=0 gmtoff=28800Copy the codeNow that you have learned all about time zones in GaussDB(DWS), you are welcome to ask questions in the forum if you have any questions about time zone usage.
5. To summarize
As a high-performance analytical database product for global users, GaussDB(DWS) supports time zone data in accordance with industry standards.
For more information about GuassDB(DWS), please search “GaussDB DWS” on wechat to follow the wechat public account and share with you the latest and most complete PB series warehouse black technology ~
Click to follow, the first time to learn about Huawei cloud fresh technology ~