Query data based on start time and end time

1) Expected effect

If there is a column of date-type data in the database, and you want to query the data of a certain period of time, how to implement this?

1.2 Implementation roadmap

Define two report parameters (one start time and one end time), and set the filter criteria to the date column is greater than or equal to the start time and the date column is less than or equal to the end time.

1. Preparing a template

1) Create a general report, create a template dataset ds1, and the SQL statement is: SELECT * FROM ORDERS

2) The form design is as follows:

3) Click Template > Template Parameters and add two report parameters, starttime and endtime. As shown below:

2. Adding widgets

Enter the parameters panel and click the "Add All" button in the upper right corner to add two date widgets, as shown below:

3. Setting filter criteria

Double-click on the A2 cell and select Filter to add the filter conditions shown below to the A2 cell. Filter conditions mean that the order date is greater than or equal to the start date and less than the end date.

Note: If the database date or time is text type, it is recommended that users convert it to date or time type, because the principle of text size comparison does not comply with the date comparison situation.

4. Effect preview

1) PC

Save the template, click "Pagination Preview", select the start time and end time, click the query button, the effect is as shown below:

2) Mobile

The end effect of App and HTML5 is as follows:

5. Completed template

For the completed template, see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\TimeScale\Query_data_based_on_start_time_and_end_time.cpt

Click to download the template: 

Query_data_based_on_start_time_and_end_time.cpt

1.Preparing templates

1) Create a general report, create a template dataset ds1, and the SQL statement is:

SELECT * FROM ORDERS where 1=1 ${if(len(starttime)=0,""," and SIGNDATE>='"+starttime+"'")}${if(len(endtime)

=0,""," and SIGNDATE<'"+endtime+"'")} 

2) The form design is as follows:

2. Adding widgets

Enter the parameters panel and click the "Add All" button in the upper right corner to add two date widgets, as shown below:

3. Effect preview

The effect is the same as in Section II of this article.

4. Completed template

For the completed template, see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\TimeScale\Query_data_based_on_start_time_and_end_time2.cpt

Click to download the template: 

Query_data_based_on_start_time_and_end_time2.cpt

1. Usually, we need to verify: the start date and end date cannot be empty, the end date must be after the start date, and the end date must be within a certain period after the start date. Otherwise, relevant information will be found in JS Validate the Values of Date Widgets

2. If you want to query data by year, month, and day in the same report, see Dynamic display parameter widget.