How does the data warehouse play happily with the "daylight saving time"?

Abstract: mainly describes the origin, development, standardization of daylight saving time and how to query the daylight saving time rules of any time zone in GaussDB.

This article is shared from HUAWEI CLOUD COMMUNITY "How to play with "Daylight Saving Time" with ", original author: leapdb.

1. Background introduction

In order to have a deeper understanding of daylight saving time, let's first look at a piece of tangled news:

Xinhuanet, Moscow, July 9th (Reporter Cao Yan) The Russian Federation Council (the upper house of parliament) passed a bill on the 9th, confirming that starting from October 26 this year, Russia's national time zone will be delayed by one hour on the current basis. Take the capital Moscow as an example. The current time is 4 hours earlier than Greenwich Mean Time. After October 26, Moscow Time will be 3 hours earlier than Greenwich Mean Time.

The bill has been passed by the State Duma (the lower house of parliament), and it is expected that the Russian president will sign the bill soon. This is Russia's new time system change after the permanent use of daylight saving time in 2011. Russia has a vast territory and spans many time zones, and society has been arguing about how to use "reasonable time". And this time system adjustment did not satisfy everyone.

For the sake of energy conservation, the former Soviet Union and Russia used daylight saving time for 30 years. Daylight saving time is also called daylight saving time, which refers to the early spring and summer when the time is adjusted forward by one hour. Its original intention is to make full use of the sun. In many high latitude regions, the sun rises earlier in spring and summer than in winter. Daylight saving time allows people to get up and go to bed early, reducing energy consumption due to lighting.

Russia regulated the use of daylight saving time from 1981 to 2011. Every year on the last Sunday in March, the clock is set one hour forward; on the last Sunday in October every year, the clock is set one hour backward. Take the capital Moscow as an example. Daylight saving time is 4 hours earlier than Greenwich time, and non-daylight saving time is 3 hours earlier than Greenwich time.

However, people have been controversial about the pros and cons of daylight saving time. For modern society, whether daylight saving time can save the total amount of energy consumption is still inconclusive. In addition, medical experts also have different opinions on how much the time change twice a year affects the circadian body clock.

Starting in 2011, Russia stopped using winter and summer time conversion. The then President ordered in 2011 that the clock would no longer be adjusted by one hour after the end of daylight saving time that year, and the daylight saving time would be used permanently. This means that the time zone of Russia is advanced by one hour, and Moscow time is 4 hours earlier than Greenwich time throughout the year. The then president explained that frequent adjustments of time disrupted the biological cycle of the human body, making people feel stressful and prone to illness.

For Russia, a vast territory, the permanent use of daylight saving time can allow residents in different regions to get more sunlight. However, most regions of Russia have high latitudes, and the daylight saving time is relatively late in winter. The daylight saving time is still used to force people to get up to work in the dark, which causes certain difficulties in life.

Since the permanent use of daylight saving time for more than two years, there are fewer and fewer supporters. In 2011, when the use of daylight saving time was stopped, a poll conducted by the Levada Center, an independent polling agency in Russia, showed that 63% of the respondents supported the permanent use of daylight saving time. However, a poll conducted by the survey agency in October last year showed that the proportion of respondents who agreed with the move fell to 41%.

Since 2012, many congressmen have submitted proposals to change the time system. After several postponement of discussions due to various reasons, the State Duma finally began to formally discuss the bill to change the time system in June, and decided to change from the permanent use of daylight saving time to the permanent use of "winter time." But this did not satisfy everyone. An earlier poll showed that about one-third of the respondents who support the use of daylight saving time and the use of "winter time" each accounted for about one-third, and another one-third of the respondents said it didn't matter.

It can be seen that daylight saving time has always been a matter of controversy. Whether to adopt daylight saving time or not, we still need to take a closer look at its causes and the degree of social development.

2. What is daylight saving time, its creation, causes, and pros and cons

Daylight saving time is also called daylight saving time, and daylight saving time (Daylight Saving Time: DST) is also called "daylight saving time system". The uniform time adopted during the implementation of this system is called "daylight saving time". Generally, in the summer when the daylight is early, the time is artificially adjusted by one hour, which can make people get up and go to bed early, reduce the amount of lighting, and make full use of light resources, thereby saving lighting power. The specific regulations of each country adopting daylight saving time are different. Nearly 110 countries around the world implement daylight saving time every year.

The process by which daylight saving time is generated and gradually adopted by many countries:

1. The outstanding American politician and diplomat, Franklin (the figure printed on the $100 bill), was accustomed to the early bed and early rise life of the American rural aristocracy during his tenure as the American ambassador to France. I saw it during a morning walk. The French didn't get up until 10 o'clock, and the night life lived until late at night.

In this way, there will be very little time to communicate and work with the French, which in his opinion is a waste of time. So he wrote a letter to the editor of the "Paris Magazine" in 1784. The letter said that the French lifestyle wasted a lot of sunshine and suggested that they go to bed and get up early, saying that they can save 64 million pounds of candles every year.

2. In 1907, the British architect William Willett formally proposed the idea of daylight saving time to the British Parliament, mainly to save energy and provide more time for training soldiers, but the Parliament did not adopt it after debate.
3. In 1916, Germany, a country advocating conservation and environmental protection, took the lead to stand up and try this plan throughout the country. As a result, it can save 15% of coal, electricity and other resources every day.
4. The United Kingdom was afraid that Germany would get more benefits from it, so it followed up with the adoption of daylight saving time, which saved about 15% of gas and electricity.
5. After Britain and Germany adopted daylight saving time, France soon followed suit.

6 In 1917, Russia introduced daylight saving time for the first time.

7. In 1918, the United States, which participated in World War I, also implemented daylight saving time, but it was cancelled immediately after the war.
8. In 1942, during the Second World War, the United States implemented daylight saving time again. From 1945 to 1966, each state determined the start and end time (date) of daylight saving time. In 1966, the US federal government reunified the start and end times (dates) of daylight saving time.
9. Most countries in Europe began to implement daylight saving time three years after the first oil crisis (1973) caused by the Fourth Middle East War in 1976.
10. Seeing the benefits of daylight saving time, 110 countries around the world have successively implemented it, including China.

It can be seen that saving money is a good thing that everyone likes.

Advantages of daylight saving time

Why is it said that daylight saving time is more energy efficient? As we all know, summer is a season in the four seasons of the year, when the daylights earlier and the darkness becomes slower. The nights are relatively short and the days are relatively long. So, if everyone can get up early in the summer and sleep for an hour late, they will be able to make full use of natural light resources and save electricity for lighting. And throughout the summer, each person will be able to save a huge amount of energy.

Disadvantages of daylight saving time

1. When daylight saving time starts and ends, people have to adjust all timekeeping instruments to speed up or down. This was a troublesome event in the old days when everyone still used mechanical watches or feature phones.
2. When the daylight saving time ends, certain times will appear twice in the day, which can easily constitute confusion. And affect the flight time.

Daylight saving time is more suitable for high latitude areas, because the sun rises in summer in high latitude areas obviously earlier than winter, so the effect of summer time will be very good. However, it is different in low-latitude regions. Not only does the implementation of daylight saving time have little effect, but it also brings many negative effects.

For example, summer nights in low latitudes are hot and humid, making it difficult to sleep. In the early morning, it is cooler and more comfortable, which is a good time to sleep peacefully. If you want to get up early at this time, it will reduce people’s sleep quality, cause people’s mental sluggishness, affect work quality, and damage physical health. People's emotions become more irritable, and a series of adverse reactions occur. China is a low-latitude country, so it is not suitable for daylight saving time.

In addition, China is still a vast country, so across five time zones, from east to west, there is a difference of five hours. If it is uniformly required to wake up 1 hour earlier, it would be better like the eastern area, but like the western area, it is equivalent to getting up before dawn...So, the quality of sleep is affected, because it is not bright when you wake up. It is impossible to discredit, wash and eat early, so the lighting and electricity that should be used will still be consumed.
And many cities in China are like a big stove in summer, which means that in summer in China, the most electricity consuming is not for lighting, but for refrigeration! So, if China implements daylight saving time, especially after a large number of fans and air conditioners appear and become popular, if the cooling power is consumed to save lighting power, the gain will not be worth the loss.

The original intention of daylight saving time is to go to bed early and get up early, to make full use of the sun, and to adjust all timing instruments faster or slower. To achieve this goal, it is undoubtedly the most inappropriate. What we should do is to adjust the suitable work and rest time and develop daily energy saving and environmental protection methods such as turning off the lights.

3. The history of the use of daylight saving time in my country

Seeing the benefits of daylight saving time, 110 countries around the world have implemented daylight saving time successively, including China. In April 1986, in order to accelerate economic development and save energy, they issued a notice to implement daylight saving time across the country. Related mature programs abroad. However, after six years of implementation, that is, in 1991, my country stopped the daylight saving time.

The following table is the timetable of my country's previous implementation of daylight saving time:

• From 1935 to 1951, from May 1 to September 30 each year.
• March 1st to October 31st, 1952.
• From 1953 to 1954, from April 1 to October 31 each year.
• From 1955 to 1956, from May 1 to September 30 each year.
• From 1957 to 1959, from April 1 to September 30 each year.
• From 1960 to 1961, from June 1 to September 30 each year.
• From 1974 to 1975, from April 1 to October 31 each year.
• July 1 to September 30, 1979.
• April 13 to September 14, 1986
• From April 12 to September 13, 1987,
• From April 10 to September 11, 1988,
• From April 16 to September 17, 1989,
• From April 15 to September 16, 1990,
• From April 14 to September 15, 1991.

4. How to query the daylight saving time of a country in GaussDB (DWS)

4.1. Time zone related system tables in GaussDB (DWS)

pg_timezone_names-system table for storing time zone information in GaussDB (DWS)

pg_timezone_abbrevs-a system table in GaussDB (DWS) that stores time zone abbreviation information

4.2. Query current (2021/03/05) time zone information that has started to use daylight saving time

Query from pg_timezone_names the time zone information that currently (2021/03/05) has started to use daylight saving time:

postgres=# select * from pg_timezone_names where is_dst='t';
         name          | abbrev | utc_offset | is_dst 
-----------------------+--------+------------+--------
 Australia/NSW         | AEDT   | 11:00:00   | t
 Australia/South       | ACDT   | 10:30:00   | t
 Australia/Yancowinna  | ACDT   | 10:30:00   | t
 Australia/Broken_Hill | ACDT   | 10:30:00   | t
 Australia/Lord_Howe   | +11    | 11:00:00   | t
 Australia/ACT         | AEDT   | 11:00:00   | t
 Australia/Melbourne   | AEDT   | 11:00:00   | t
 Australia/Adelaide    | ACDT   | 10:30:00   | t
 Australia/LHI         | +11    | 11:00:00   | t
 Australia/Currie      | AEDT   | 11:00:00   | t
 Australia/Hobart      | AEDT   | 11:00:00   | t
 Australia/Tasmania    | AEDT   | 11:00:00   | t
 Australia/Sydney      | AEDT   | 11:00:00   | t
 Australia/Canberra    | AEDT   | 11:00:00   | t
 Australia/Victoria    | AEDT   | 11:00:00   | t
 NZ                    | NZDT   | 13:00:00   | t
 NZ-CHAT               | +1345  | 13:45:00   | t
 Europe/Dublin         | GMT    | 00:00:00   | t
 Eire                  | GMT    | 00:00:00   | t
 Chile/Continental     | -03    | -03:00:00  | t
 Chile/EasterIsland    | -05    | -05:00:00  | t
 Antarctica/McMurdo    | NZDT   | 13:00:00   | t
 Antarctica/South_Pole | NZDT   | 13:00:00   | t
 America/Asuncion      | -03    | -03:00:00  | t
 America/Santiago      | -03    | -03:00:00  | t
 Pacific/Apia          | +14    | 14:00:00   | t
 Pacific/Chatham       | +1345  | 13:45:00   | t
 Pacific/Easter        | -05    | -05:00:00  | t
 Pacific/Auckland      | NZDT   | 13:00:00   | t
 Pacific/Norfolk       | +12    | 12:00:00   | t
(30 rows)

Query from pg_timezone_abbrevs the time zone information that currently (2021/03/05) has started to use daylight saving time:

postgres=# select * from pg_timezone_abbrevs where is_dst='t';
 abbrev | utc_offset | is_dst 
--------+------------+--------
 ACDT   | 10:30:00   | t
 ACSST  | 10:30:00   | t
 ADT    | -03:00:00  | t
 AEDT   | 11:00:00   | t
 AESST  | 11:00:00   | t
 AKDT   | -08:00:00  | t
 ALMST  | 07:00:00   | t
 AWSST  | 09:00:00   | t
 AZOST  | 00:00:00   | t
 BDST   | 02:00:00   | t
 BRST   | -02:00:00  | t
 BST    | 01:00:00   | t
 CADT   | 10:30:00   | t
 CDT    | -05:00:00  | t
 CEST   | 02:00:00   | t
 CETDST | 02:00:00   | t
 CHADT  | 13:45:00   | t
 CLST   | -03:00:00  | t
 CLT    | -03:00:00  | t
 EASST  | -05:00:00  | t
 EAST   | -05:00:00  | t
 EDT    | -04:00:00  | t
 EEST   | 03:00:00   | t
 EETDST | 03:00:00   | t
 EGST   | 00:00:00   | t
 FJST   | 13:00:00   | t
 FNST   | -01:00:00  | t
 IDT    | 03:00:00   | t
 KDT    | 10:00:00   | t
 KGST   | 06:00:00   | t
 LHDT   | 11:00:00   | t
 MDT    | -06:00:00  | t
 MEST   | 02:00:00   | t
 MESZ   | 02:00:00   | t
 METDST | 02:00:00   | t
 MSD    | 04:00:00   | t
 MUST   | 05:00:00   | t
 NDT    | -02:30:00  | t
 NZDT   | 13:00:00   | t
 PDT    | -07:00:00  | t
 PKST   | 06:00:00   | t
 PMDT   | -02:00:00  | t
 PYST   | -03:00:00  | t
 PYT    | -03:00:00  | t
 SADT   | 10:30:00   | t
 ULAST  | 09:00:00   | t
 UYST   | -02:00:00  | t
 UZST   | 06:00:00   | t
 WADT   | 08:00:00   | t
 WDT    | 09:00:00   | t
 WETDST | 01:00:00   | t
 WGST   | -02:00:00  | t
 YEKST  | 06:00:00   | t
(53 rows)

4.3. Query the historical records of summer time in a certain time zone in GaussDB (DWS)

In GaussDB (DWS), we can query whether the current time zone has adopted daylight saving time information through SQL, but if we want to know which time zone has used daylight saving time in history, we can use external tools to do so.

zdump is a binary time file parsing tool that comes with the Linux operating system. Through it we can parse a binary time zone file, view the time difference of a certain time zone relative to UTC and the daylight saving time rules.

GaussDB (DWS) uses a standard time zone data information library consistent with GNU, so the binary time zone files in GaussDB (DWS) can also be parsed by this tool. From the picture above, we can see the history of my country's adoption of daylight saving time.

5. Summary

In summary, GaussDB (DWS), as a high-performance analytical database product for global users, supports daylight saving time in compliance with industry standards. GaussDB (DWS) has its own time zone information database, which can easily and conveniently query the daylight saving time information.

For more information about GuassDB (DWS), welcome to search "GaussDB DWS" on WeChat and follow the WeChat official account to share with you the latest and most complete PB-level data warehouse black technology~

Click to follow, and get to know the fresh technology of