Shulou(Shulou.com)05/31 Report--

This article will explain in detail how sqlserver/mysql statistics continuous time data by day, hour and minute. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

First, the words written in the front

Recently, the company needs to view the data by day and hour, and the peak data of the time period can be seen visually. Received the demand, began crazy Baidu search, but the search materials are not clear, need to summarize and dig one of the important information.

With regard to sqlserver, there are a few points you need to make clear (Baidu, which you don't understand):

What is master..spt_values? What can it be used for?

How to generate continuous time periods (year, month, day, hour, minute)

Second, what is master..spt_values? What can it be used for?

The main function of a relatively fixed general-purpose table for taking numbers is to take consecutive numbers, but one drawback is that it can only get 2047. You can execute the following statement to know what it means.

Select number from master..spt_values where type='p'

Third, how to produce continuous time periods (year, month, day, hour, minute)

In the actual application, at present, it mainly produces a continuous period of time. I have prepared the common operations, and the following sentences are shown separately.

Annual production of continuous SELECT substring (CONVERT (NVARCHAR (10), DateAdd (YEAR, number, '2016-01-01), 120) AS GroupDay,type FROM master..spt_values WHERE type =' p 'AND number

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.