ZX:rishistar programming blocks Java/Wordpress programming tips and tricks

Selecting MySQL rows within a Time Range for a Java Program

Earlier I discussed selecting rows from a table within a specified date range. What if you need to select rows from within a time range?

Again a SELECT statement with a WHERE clause is needed. The  method below will give the statement for the start and end times of the statement. Unlike the earlier example this has a DateFormat to supply the String required to generate the SQL statement.

GregorianCalendar calStart = new GregorianCalendar() ;
 calStart.set( Calendar.HOUR_OF_DAY, 18 ) ;
 calStart.set( Calendar.MINUTE, 1 ) ;
 String sql = "SELECT * FROM " + dbh.userTable.tableName +
          " WHERE status=1 AND "
          + SQLStrings.getWhereBetweenDates( "signup_time", calStart ) ;

.....

public static String getWhereBetweenDates( String columnName, GregorianCalendar calStart)
{
    GregorianCalendar now = new GregorianCalendar() ;
    StringBuffer sb = new StringBuffer( columnName + " BETWEEN " ) ;
    SimpleDateFormat df = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ) ;
    String beforeDateString = df.format( calStart.getTime() ) ;
    String afterDateString = df.format( now.getTime() ) ;
    sb.append( beforeDateString + " AND " + afterDateString ) ;
    return sb.toString() ;
}

Note that the DateFormat String here was determined by what was present in the MySQL database for that particular column. YMMV – so if there are problems maybe check your database table to see what particular format that column takes.