Converting Epoch (Unix) Time to Date/Time Using Excel for Jmeter results

In the field of performance testing, interpreting raw data often includes converting epoch or UNIX time into a human-readable format. Excel offers a straightforward way to handle this conversion. Here's a simplified guide:

A client recently requested response time data with timestamps in an easy-to-read format. I used JMeter to conduct the testing and saved the results in a .jtl file. The steps below explain how to transform UNIX timestamps into a digestible date and time format using Excel.

Step 1: Access and Prepare the Data

• Open the JMeter .jtl log file in a text editor such as Notepad or Notepad++.
• Copy the content of the file and paste it into a new Excel spreadsheet.

Step 2: Split the Data

• Highlight all the data in the Excel sheet.
• Go to the 'Data' tab and choose 'Text to Columns.'
• In the 'Convert Text to Columns Wizard,' select 'Delimited' and click 'Next.'
• Choose 'Comma' as the delimiter and follow the wizard through to the end.

Step 3: Format the Timestamp Column

• Change the format of the timestamp column (usually column A) from 'General' to 'Number' by right-clicking the column and selecting 'Format Cells' to make the adjustment.

Step 4: Parse the Timestamp

• Insert a new column between the timestamp column and the next column.
• In the first cell of the new column (e.g., B2), enter the formula =LEFT(A2,10) & "." & RIGHT(A2,3) to separate the UNIX timestamp into seconds and milliseconds.
• Drag the formula down to apply it to the entire column.

Step 5: Convert to Date/Time Format

• Add another new column between the new column and the next one.
• In the first cell of this additional column (e.g., C2), input the formula =(((B2/60)/60)/24)+DATE(1970,1,1) to convert the parsed timestamp into a readable date/time format.
• Drag the formula down to fill in the rest of the column.

Step 6: Format the Date/Time Column

• Adjust the format of the date/time column (e.g., column C) to your preference using the 'Custom' format option.

Step 7: Review and Finalize

• Check that the date/time data in column C is now presented in a human-readable format, converted from the original UNIX timestamps.

Happy Testing !

How to Convert Postman Collections to JMeter Scripts

Are you looking to transition your API collection from Postman to Apache JMeter for performance testing? Whether you prefer an online solution or are comfortable with offline setup, we've got you covered. In this guide, we'll explore two methods to seamlessly convert your Postman collections into JMeter scripts.

Export Postman Collection:

1. Open Postman and navigate to the collection you want to export.
2. Click on the three-dot menu next to the collection name and select Export.
3. In the export dialog, choose the format v2.1 or v2.0 (either should work, but v2.1 is preferred for the latest collections).
4. Click Export to save the collection as a JSON file.

Option 1: Loadium Application (Online)

Loadium, a versatile load testing tool supporting various performance test types, including JMeter, Gatling, and Selenium, offers a convenient online solution for converting Postman collections to JMX projects.

Here's how it works:

1. Visit Loadium's website and sign up for a trial account.

2. Once logged in, navigate to the "More Options" section and select "Convert to JMX."

3. Upload your Postman collection.

4. Follow the on-screen prompts to initiate the conversion process.

5. Download the generated JMX file.

6. Open Apache JMeter and import the downloaded JMX file to begin your performance testing.

Option 2: postman2jmx (Offline)

1. Typically, you would run a command like java -jar postman-to-jmx-converter.jar -i postman_collection.json -o jmeter_script.jmx.
2. Replace postman_collection.json with the path to your exported Postman collection and jmeter_script.jmx with the desired output file name.

If you prefer an offline approach and are comfortable with a bit of setup, postman2jmx, available on GitHub, provides an excellent solution for converting Postman collections to JMeter scripts.

Here's how to set it up on MacOS:

1. Install Homebrew, Maven, and Java if not already installed.

2. Clone the postman2jmx repository from GitHub.

3. Build postman2jmx using Maven.

4. Navigate to the "target" folder in the terminal.

5. Convert your Postman collection to a JMX file using the provided command.

6. Open the converted JMX file in Apache JMeter to proceed with your performance testing.

Happy testing!