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

Selecting MySQL Rows with a Timestamp Column on a Single Day or Month from your Java Program

You have a MySQL database table with a Timestamp column, and you need to select entries for a single day, month or year. As indicated in previous posts, you can construct MySQL statements that allow you to access items from a certain date/month/year.

This can be achived using the following.

String sql =
“SELECT * FROM orders WHERE day(time_outgoing)=” + day ;
Statement st = con.createStatement() ;
ResultSet rs = st.executeQuery(sql) ;

Something similar can be done for months and years, and Java’s Calender class provides fields for moth access. However note that the java.util.Calender fields for months are indexed from 0, so the actual integer value of Calender.MAY is 4 and not 5.

int month = Calender.MAY ; //want entries for may

//increase the month by 1 to access values

String sql =
“SELECT * FROM orders WHERE month(time_outgoing)=” + (month+1) ;
Statement st = con.createStatement() ;
ResultSet rs = st.executeQuery(sql) ;

OR

SELECT *
FROM optout75308
WHERE logtime
BETWEEN “2010-05-21″
AND “2010-05-22″
AND (
contact_method = ‘ODWBPRESS1′
OR contact_method = ‘SERV2OWDBy’
)