I. Overview
1) Expected Effect
When a table in the database has a time field, and almost every minute and every second there will be a data generated, in order to reduce the amount of data displayed in the report, we can filter the data by time interval and only display part of the data.
For example, enter the time query range in starttime and endtime, enter the interval number 3 in number, and filter the data at the interval of 3 seconds, as shown in the picture below:
2) Implementation roadmap
1) Add dataset parameters in SQL query, assign values to the number parameters through the widget in the parameter panel, and SQL can find out the qualified data according to the parameters passed.
2) Add conditional attributes to cells, and display data to be displayed by hiding cells.
II. MySQL database example
SQL statements vary with databases. The following uses the MySQL database as an example. Suppose you already have a database table, Datetest, with one entry every one second, as shown below:
1. Creating a dataset
Create a new template and create a new dataset ds1, as shown below:
The SQL statement is:
SELECT *,timestampdiff(SECOND,date_format(datets, '%Y-%m-%d'),date_format(datets, '%Y-%m-%d %H:%i:%s')) % ${number} as jg
FROM
datetest
where
1=1
${if(len(starttime)==0,"","and date_format(datets,'%Y-%m-%d %H:%i:%s') >='"+ starttime +"'")}
${if(len(endtime)==0,"","and date_format(datets,'%Y-%m-%d %H:%i:%s') <='"+ endtime +"'")}
Note: Dates in the database are stored in date format or string format. The writing method of SQL statements varies depending on the actual situation.
SQL statement description:
1) There are three data set parameters in the SQL statement, which are number, startTime and endTime respectively. The default value is 1, starttime is used to pass the starttime, and endtime is used to pass the endtime, which will correspond to the text widget box and time filter box on the parameter panel.
2) In MySQL,timestampdiff (year, quarter, month, day, hour, minute, second, etc.); The date_format function is used to display date/time data in different formats.
3) % is the mod function, which realizes the function of extracting data at a certain time interval through the mod of time difference.
2. Designing template styles
1) Drag the dataset field of number ds1 into cell A1, B1 and C1, and set cell style, as shown below:
2) Click the C2 cell and add the conditional attribute: when C2 is not 0, set the row height to 0, that is, hide the current row, as shown below:
3. Setting parameter widgets
Click "Parameter Panel", set widget properties after adding parameter widgets, starttime, endtime are date widgets, and number is text widget. As shown below:
4. Effect preview
After saving the template, click "Pagination Preview" and enter the start time, end time, and interval number. The query result is as shown in Section I.
III. Other SQL examples of databases
1. SQL Server database
SELECT *,datediff(s,convert(varchar(100),datets,111),convert(varchar(100),datets,120)) % ${number} AS jg
FROM
dbo.datetest
WHERE 1 = 1
${IF(len(starttime) == 0,"","and CONVERT(varchar(100),datets,120)>='" + starttime + "'")}
${IF(len(endtime) == 0,"","and CONVERT(varchar(100),datets,120)<='" + endtime + "'") }
Note: Dates in the database are stored in date format or string format. The writing method of SQL statements varies depending on the actual situation.
SQL statement description:
1) There are three data set parameters in the SQL statement, which are number, startTime and endTime respectively. Where number is used to pass the number of filter intervals, the default is set to 1, starttime is used to pass the starttime, and endtime is used to pass the endtime.
2) In SQL Server, datediff returns the time between two dates. The parameters can be year, quarter, month, day, hour, minute, second, etc. The CONVERT() function is a generic function for converting dates to new data types, and can also display dates in different formats.
3) % is the mod function, which realizes the function of extracting data at a certain time interval by mod time difference.
2. Oracle Database
SELECT *,to_char(datets,'yyyy-mm-dd hh24:mi:ss'),mod((datets - to_date(to_char(datets, 'yyyy-mm-dd'),'yyyy-mm-dd'))
* 24 * 60 * 60,${number}) AS jg
from dbo.datetest
where 1=1
${IF(len(starttime) == 0,"","and to_char(datets,'yyyy-mm-dd')>='" + starttime + "'")}
${IF(len(starttime) == 0,"","and to_char(datets,'yyyy-mm-dd')<='" + endtime + "'") }
Note: Dates in the database are stored in date format or string format. The writing method of SQL statements varies depending on the actual situation.
SQL statement description:
1) There are three data set parameters in the SQL statement, which are number, startTime and endTime respectively. Where number is used to pass the number of filter intervals, the default is set to 1, starttime is used to pass the starttime, and endtime is used to pass the endtime.
2) In SQL Server, the to_date function converts a character type to a date type in a certain format, and the to_char function converts a date type to a character type in a certain format. Datets-to_date(to_char(datets, 'yyyy-mm-dd'),'yyyy-mm-dd')returns the number of days, so * 24 * 60 * 60 is used to obtain the number of seconds.
3) MOD is a mod function, which realizes the function of extracting data at a certain time interval through the mod of time difference.
