Run massively scalable performance tests on web, mobile, and APIs

Request a Demo
May. 16th, 2018

Testing Dates from a MySQL Database with JMeter

In my previous blog post  “Creating and Testing Dates in JMeter - Learn How”, I showed how to create dates for testing APIs in Apache JMeter™. This article will show how to test the creation of tables with dates and how to test data retrieval from a database by dates. By testing these actions in JMeter you can ensure your APIs correctly write to the database, delete from it and so on.

 

To work with dates, the MySQL database will be used. In the database, the following data types exist for the Date and Time:

 

Data TypesDescription
DATESupported range: from '1000-01-01' to '9999-12-31'
Display Format: 'YYYY-MM-DD'
DATETIMESupported range: from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'
Display format: 'YYYY-MM-DD HH:MM:SS[.fraction]
TIMESupported range: from '-838:59:59.000000' to '838:59:59.000000'
Display format: HH:MM:SS[.fraction]
TIMESTAMPSupported range: from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC
Display format: 'YYYY-MM-DD HH:MM:SS[.fraction]

 

Fraction is the value of microseconds with an accuracy of 6 digits.

 

In order to create a table in a database with date and time fields, you need to do the following.

 

1. Configure the connection to the database

 

Configure the connection as described in the blog post “MySQL Database and JMeter - How to Test Your Connection”.

 

2.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Update Statement

 

In this step, we will send a query to the database to create a table.

 

In the JDBC Request, add the following code:

 

CREATE TABLE sakila.for_test( DATE_TEST DATE, DATETIME_TEST DATETIME(3), TIME_TEST TIME(2), TIMESTAMP_TEST TIMESTAMP(6), TIMESTAMP_WITHOUT_MICROS TIMESTAMP)  

 

testing dates in a db with jmeter

 

The code shown above does the following:

CREATE TABLE - The operator that creates the table in the database

 

sakila - Database name. Choose the name your database has.

 

for_test - The name of the table to be created

 

DATE_TEST DATE:

  • DATE_TEST - Column name in the table
  • DATE - The data type for the values that will be stored in the column

 

DATETIME_TEST DATETIME(3):

  • DATETIME_TEST - Column name in the table
  • DATETIME - The data type for the values that will be stored in the column
  • (3) - The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 3 digits

 

TIME_TEST TIME(2):

  • TIME_TEST  - Column name in the table
  • TIME - The data type for the values that will be stored in the column
  • (2) - The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 2 digits

 

TIMESTAMP_TEST TIMESTAMP(6):

  • TIMESTAMP_TEST  - Column name in the table
  • TIMESTAMP - The data type for the values that will be stored in the column
  • (6) - The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 6 digits

 

TIMESTAMP_WITHOUT_MICROS TIMESTAMP:

  • TIMESTAMP_WITHOUT_MICROS  - Column name in the table
  • TIMESTAMP - The data type for the values that will be stored in the column. For this column microsecond accuracy is not specified. In this case, the values in this column will not display microseconds

 

3.  Add a View Results Tree

 

Thread Group -> Right Click -> Add -> Listener -> View Results Tree

 

This step will display the request that JMeter sends to the database and the response that it receives from the database.

 

load testing dates in a database

 

After starting the test, we will see a result, as shown in the image below:

 

writing dates to a database with jmeter

 

0 updates means that we successfully created the table in the database

 

If we go to the database, we will also see the created table, as shown in the image below:

 

api testing dates in a database with jmeter

 

In order to add rows with date and time to the created table, i.e filling in the columns with data, you need to do the following.

 

4.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Update Statement

 

In the JDBC Request, add the following code:

 

INSERT INTO sakila.for_test VALUES ('2018-04-30', '2018-09-08 18:52:07.769', '18:52:07.769', '2018-09-08 18:52:07.769111', '2018-09-08 18:52:07');  

 

deleting dates from a database with jmeter

 

The code shown above does the following:

INSERT INTO - The statement that allows adding data to a table

 

sakila - Database name

 

for_test - The name of the table that data will be added to

 

VALUES - Specifies the values to add to the table

 

('2018-04-30', '2014-09-08 18:52:07.769', '18:52:07.769', '2014-09-08 18:52:07.769111', '2014-09-08 18:52:07') - The values to be written to the columns, in our case.

 

2018-04-30 - will be written inthe column DATE_TEST

 

2014-09-08 18:52:07.769 - will be written in the DATETIME_TEST column, etc.

 

When adding date and time values to the database, the following rules exist:

  • Values must be passed only in the format that the corresponding data type supports. For example, if the DATE data type is displayed in the 'YYYY-MM-DD' format, then the value should be passed in the same form (2018-04-30). If you pass the value in the form 30-04-2018, you will get an error of the following kind: "Data truncation: Incorrect date value: '30 -04-2018' for column 'DATE_TEST' at row 1".
  • If the value is passed as a string (the value is specified in single quotes), then you can use different delimiters. For example, '2018/04/30', '2018@04@30' or '20180430'. Regardless of which separator is used, the value of the date and time in the database will only be displayed in the format that the data type supports, which is “-”.
  • If the value is passed as a number (the value is specified without single quotes), then a delimiter is not used. For example, if we need to add the value '2018-04-30' in numerical form, then it must be passed as 20180430. After adding to the database, this value will be displayed as 2018-04-30. If you want to add the date and time ('2018-09-08 18:52:07') in numerical form, then the value is passed as 20180908185207.
  • For columns with data type DATETIME and TIMESTAMP, values can be passed without specifying a time. For example, if we pass a value of type 2018-04-30 (without time), then in the database this value will be displayed in the form 2018-04-30 00: 00: 00.000
  • For columns with data type DATE, we can pass the value with the time, but in this case the time will not be displayed in the database. For example, if we pass the value in the form 2018-04-30 18:00:54.234, only 2018-04-30 will be displayed in the database

 

After starting the test, we will see a result, as shown in the image below:

 

retrieving dates from a database with jmeter

 

1 updates means that we successfully added data to the table.

 

If we go to the database and execute SELECT * FROM sakila.for_test, we'll see the result, as shown in the image below:

 

creating tables with dates in a database with jmeter

 

In order to get data from a database about a specific date or time, you need to do the following

 

5.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Select Statement

 

In the JDBC Request, add the following code:

 

SELECT * FROM sakila.for_test where DATETIME_TEST = '2018-09-08 18:52:07.769' 

 

testing databases in jmeter with the jdbc request

 

The code shown above does the following:

 

SELECT - The statement to retrieve data from a table

 

* - Selects all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

 

FROM - The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.for_test

 

sakila -  Database name

 

for_test - The name of the table we need to retrieve the data from

 

WHERE - The condition for selecting data. In our case, the condition is DATETIME_TEST = '2018-09-08 18:52:07.769'

 

DATETIME_TEST - The name of the column in the table for_test

 

'2018-09-08 18:52:07.769' -  The value that is contained in the DATETIME_TEST column

 

After starting the test, we will see a result, as shown in the image below:

 

performance testing databases with jmeter

 

The image above shows the result of getting data from the database, where the value in the DATETIME_TEST column is '2018-09-08 18:52:07.769'

 

In order to select data from a table without specifying, for example, the month, date or time, you need to do the following:

 

6.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Select Statement

 

In the JDBC Request, add the following code:

 

SELECT * FROM sakila.payment where payment_date like '2005-05-25%'

 

api testing databases with jmeter

 

The code shown above does the following:

 

SELECT - The statement to retrieve data from a table

 

* - Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

 

FROM - The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

 

sakila -  Database name

 

payment - The name of the table from which need to retrieve the data (created when configuring the MySQL database)

 

WHERE - The condition for selecting the data. In our case, the condition is payment_date like '2005-05-25%'

 

like  -  The statement that performs the search the for data in the table according to a specific template. In our case, the template is '2005-05-25%' in the column payment_date

 

% - This means that any character can appear after the value 2005-05-25. The % character can also be used at the beginning of the value, for example, %05-25%

 

After starting the test, we will see a result, as shown in the image below:

 

testing your databases with jmeter

 

The image above shows the result of getting data from the database, where the column payment_date contains '2005-05-25'

 

In order to get data from a table for a specific date range or time, you need to do the following

 

7.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Select Statement

 

In the JDBC Request, add the following code:

 

SELECT * FROM sakila.payment where payment_date between '2005-05-25 00:00:40' and '2005-05-25 00:43:11' order by payment_date 

 

database api testing with jmeter

 

The code shown above does the following:

 

SELECT - The statement to retrieve data from a table

 

* - Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

 

FROM - The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

 

sakila -  Database name

 

payment - The name of the table from which need to retrieve the data

 

WHERE - The condition for selecting the data. In our case, the condition is payment_date between '2005-05-25 00:00:40' and '2005-05-25 00:43:11'

 

between and - The statement that searches for data in a table in the specified range. In our case, data will be received where the column payment_date contains values that fall within the range from '2005-05-25 00:00:40' to '2005-05-25 00:43:11' (inclusive).

 

order by - The statement that sorts in a specified column. In our case, the sorting will be performed on the payment_date column in the order of the increase, from earlier to latest.

 

After starting the test, we will see a result, as shown in the image below:

 

dates in database testing results

 

The image above shows the result of getting data from the database, where the column payment_date contains values that fall within the range from '2005-05-25 00:00:40' to '2005-05-25 00:43:11' (inclusive).

 

In order to get data from a table for a specific date range or time, but do not include the beginning and end values of the range, you need to do the following

 

8.  Add a JDBC Request

 

Thread Group -> Right Click -> Add -> Sampler -> JDBC Request

 

Query Type -> Select Statement

 

In the JDBC Request, add the following code:

 

SELECT * FROM sakila.payment where payment_date > '2005-05-25 00:00:40' and payment_date < '2005-05-25 00:43:11' order by payment_date

 

api testing databses with jmeter results

 

The code shown above does the following:

 

SELECT - The statement to retrieve data from a table

 

* - Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

 

FROM - The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

 

sakila -  Database name

 

payment - The name of the table from which we need to retrieve the data

 

WHERE - The condition for selecting data. In our case, the condition is payment_date > '2005-05-25 00:00:40' and payment_date < '2005-05-25 00:43:11'

 

payment_date - The name of the column in the table payment

 

order by - The statement that sorts in the specified column. In our case, the sorting will be performed on the payment_date column in the order of the increase.

 

After starting the test, we will see a result, as shown in the image below:

 

database testing with jmeter guide

 

The image above shows the result of getting data from the database, where the column payment_date contains values that fall into the range greater than '2005-05-25 00:00:40', but less than '2005-05-25 00:43:11'

 

For working in the MySQL database with dates and time, there are a lot of functions that you can read by clicking on the link.

 

Follow our Database testing with JMeter series:

 

Testing Your Database with BlazeMeter

 

You can also use BlazeMeter to test different aspects of your database in parallel by using our Multi Test in BlazeMeter. Read more here.

 

database testing with blazemeter

 

Then, you can share your tests and work on them with your team members, run your test in the cloud and get insightful reports that show KPIs in real-time and over time. Request a demo to learn more, or put your URL in the box below and your test will start in minutes.

     
arrow Please enter a valid URL

Interested in writing for our Blog? Send us a pitch!