Programming and So

Consider following SQL queries:

select count(1)
from request
where request_date between
to_date(?, 'DD-MM-YYYY HH24:MI:SS') and
to_date(?, 'DD-MM-YYYY HH24:MI:SS')

select count(1)
from request
where request_date between ? and ?

It seems both queries should be similar. Moreover, I thought the second one would be the fastest. However, the sample below shows another reality.

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * Sample class demonstrating Oracle 10g date manipulation
 */
public class OracleTest extends JdbcDaoSupport {

  // Query using Oracle date conversion
  static final String countRequests1 =
    "select count(1) " +
        "from request " +
    "where request_date between to_date(?, 'DD-MM-YYYY HH24:MI:SS') and " +
                            "to_date(?, 'DD-MM-YYYY HH24:MI:SS')";

  // Query using JDBC date conversion
  static final String countRequests2 =
    "select count(1) " +
        "from request " +
    "where request_date between ? and ?";

  // Compare two queries
  private void doQueries() throws Exception {

    // Oracle Datasource
    OracleDataSource ds = new OracleDataSource();
    ds.setURL("jdbc:oracle:thin:@127.0.0.1:2000:PREMIDBP");
    ds.setUser("sdf_monitor");
    ds.setPassword("sdf_monitor");
    SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(ds);

    // Date parameters
    Calendar c = Calendar.getInstance();
    Date dateTo = (Date)c.getTime().clone();
    c.add(Calendar.MINUTE, -1);
    c.set(Calendar.MILLISECOND, 0);
    Date dateFrom = c.getTime();

    // String parameters
    String dateFromString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateFrom);
    String dateToString = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss").format(dateTo);

    // Query countRequests1
    long initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests1, dateFromString, dateToString);
    System.out.println("Query 1: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

    // Query countRequests2
    initTime = System.nanoTime();
    jdbcTemplate.queryForInt(countRequests2, dateFrom, dateTo);
    System.out.println("Query 2: " + (System.nanoTime() - initTime) / 1000000 + " ms.");

  }

  // Launcher
  public static void main(String... args) throws Exception {
        new OracleTest().doQueries();
  }

}

With my data results are:

Query 1: 1208 ms.
Query 2: 20038 ms.


Curious behaviour.

Edited!

An explanation can be always found (Spanish link): JDBC 9.x y las variables de Timestamp de Java