How do you pass in timestamp variable into JDBC request for jmeter

Question

I am learning to use jmeter (for the first time) to do some performance testing against our geospatial postgres (postgis) database. I want to use a JDBC Request sampler sql query to continually hit the database, however I need to alter the timestamps in the query, the problem is they aren't normal parameters, they are inside a string in a function call.

SELECT int_temp_f,
       observed_utc,
       encode(ST_AsBinary(ST_Force_2D("geometry_4326"),'NDR'),'hex') AS geom,
       "ogc_featureid"
FROM fn_surfaceobs_temperature(ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326),
                                NULL,
                                0,
                                'latest',
                                'observed >= ''2015-06-08 14:00:00Z'' AND observed < ''2015-06-09 15:00:00Z'' ',
                                TRUE)
WHERE (geometry_4326 && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326))
ORDER BY COALESCE(priority,0) DESC, observed DESC;

How can I use jmeter variables to replace a user definition inside the string?

'observed >= ''2015-06-08 14:00:00Z'' AND observed < ''2015-06-09 15:00:00Z'' ',

I want to do something like this:

'observed >= ''${start_timestamp}'' AND observed < ''${end_timestamp}'' ',

Is this even possible?

Ultimately I want to update the timestamps to always be 'today'. But after looking into how to do timestamp variable in a normal SQL substitution situation I think I'm ok settling for just having to replace the timestamps as a user defined variable for now.

Answer 1

You can indeed use jmeter vars in JDBC queries.

SELECT int_temp_f,
       observed_utc,
       encode(ST_AsBinary(ST_Force_2D("geometry_4326"),'NDR'),'hex') AS geom,
       "ogc_featureid"
FROM fn_surfaceobs_temperature(ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326),
    NULL,
    0,
    'latest',
    'observed >= ''${StartDate}'' AND observed < ''${EndDateHour}'' ',
    TRUE)
WHERE (geometry_4326 && ST_GeomFromText('POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))',4326))
ORDER BY COALESCE(priority,0) DESC, observed DESC;

The trick here is to get a valid timestamp. As RaGe pointed out above, beanShell is the answer.

You can create a simple user assigned variable like this:

${__BeanShell(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()))}

OR in my case, I needed to do some additional tinkering to the timestamps so I created a beanshell preprocessor. https://stackoverflow.com/a/21847313/2532408 has a couple explanations.

Here is what I put in mine:

import java.text.SimpleDateFormat; 
import java.util.Date; 
import java.util.TimeZone;
import java.util.Calendar;

int backhour = -5
int forehour = 1
int foremin = 5

Date date = new Date();
date.setDate(date.getDate()); 
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");  // needs to be 2015-06-09 15:00:00Z
Calendar c = Calendar.getInstance(); // get Java Calendar instance
c.setTime(date); // set Calendar time to now
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);

c.add(Calendar.HOUR, backhour); // add -X hours
df.setTimeZone(TimeZone.getTimeZone("UTC")); // set the dateformat to use UTC
String startDate = df.format(c.getTime());   //create string of calendar object
vars.put("StartDate",startDate);   // set the jmeter var to StartDate

//set the next var 1 hour ahead
c.add(Calendar.HOUR, forehour); // add 1 hour 
String endDateHour = df.format(c.getTime());  //create string of calendar object
vars.put("EndDateHour",endDateHour);   // set the jmeter var to EndDate
c.add(Calendar.HOUR, -forehour); // minus the hour so we can resuse c

Answer 2

You can use JMeter's __time() function right inside your SQL query, it returns current date in the form controllable by a SimpleDataFormat pattern.