如何从LoadRunner各数据表中提取特定时间段的数据？

Got it, let's break this down—dealing with LoadRunner's database schema can be tricky, especially when it comes to time formats and table relationships. Here's how to tackle your problem step by step:

1. First, Decode the End Time Column in Event_meter #

The most common issue here is that LoadRunner stores End Time as a millisecond-precision Unix timestamp (a big integer representing milliseconds since the Unix epoch: 1970-01-01 00:00:00 UTC) or sometimes as an offset from the test's start time. Let's confirm which one you're dealing with:

• First, check the data type of the column using this SQL (adjust for your database type):

  -- For MySQL/MariaDB
  SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'Event_meter' AND COLUMN_NAME = 'End Time';
  
  -- For SQL Server
  SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'Event_meter' AND COLUMN_NAME = 'End Time';
  

If it's a Unix timestamp (bigint): #

Convert it to a human-readable datetime with these database-specific functions:

• MySQL/MariaDB: FROM_UNIXTIME(End Time/1000) (divide by 1000 to convert ms to seconds)
• SQL Server: DATEADD(ms, End Time, '1970-01-01')
• Oracle: TO_DATE('1970-01-01', 'YYYY-MM-DD') + (End Time/1000/60/60/24)

If it's an offset from the test start time: #

You'll need to join with the Run table to get the test's start time, then add the offset:

-- Example for SQL Server
SELECT DATEADD(ms, em.`End Time`, r.StartTime) AS EndTimeReadable
FROM Event_meter em
JOIN Run r ON em.RunID = r.RunID
WHERE r.RunID = YOUR_TEST_RUN_ID;

2. Query for the Login Transaction in Your Target Time Window #

Once you can parse the time, you need to join Event_meter with tables that hold transaction names to filter for Login. Here's a complete example (using MySQL, adjust for your DB):

SELECT
  t.TransactionName,
  em.ResponseTime, -- Or any metric you need (e.g., Throughput, Errors)
  FROM_UNIXTIME(em.`End Time`/1000) AS EndTimeReadable,
  em.RunID
FROM
  Event_meter em
JOIN
  Event e ON em.EventID = e.EventID -- Links meter data to event records
JOIN
  Transaction t ON e.TransactionID = t.TransactionID -- Links events to transaction names
WHERE
  t.TransactionName = 'Login'
  -- Filter for your target time window (adjust timezone if needed)
  AND CONVERT_TZ(FROM_UNIXTIME(em.`End Time`/1000), 'UTC', 'Asia/Shanghai') 
      BETWEEN '2024-05-20 09:00:00' AND '2024-05-20 10:00:00';

Key Notes: #

• Timezone Adjustment: Unix timestamps are UTC, so use CONVERT_TZ (MySQL) or equivalent functions to match your local test timezone.
• Schema Variations: Some LoadRunner versions prefix tables with lr_ (e.g., lr_Event_meter, lr_Transaction). If the query fails, check your actual table names.
• Filter by Run ID: If you have multiple test runs, add AND em.RunID = YOUR_RUN_ID to narrow it down to the specific 24-hour test.

3. Troubleshooting Tips #

• If you still can't parse End Time, try selecting a few raw values: SELECT End Time FROM Event_meter LIMIT 5; If the numbers are small (e.g., 3600000 for 1 hour), it's definitely an offset from the test start time.
• Verify table relationships using your LoadRunner database schema docs (most versions include a schema reference in the help center).

内容的提问来源于stack exchange，提问作者suv3ndu