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’
)

Reading a TimeStamp Value from a MySQL Database in your Java Program

So, you’ve written your database table to have a Timestamp column in it. Often this can be used to log when an entry is created or updated. This post will detail a couple of things that may be useful in doing more than just having it logged.

Java provides a java.sql.Timestamp class that can be used to read and alter timestamp values.

So if your database column is called signup_time, it can be read out as follows

import java.sql.Timestamp;

…

        Statement st = con.createStatement() ;
        ResultSet rs = st.executeQuery(  “SELECT * FROM subscribers”  ) ;
        StandardSubscriptionUser users[] = new StandardSubscriptionUser[ count ] ;
        rs.beforeFirst() ;
        while( rs.next() && count>0 )
        {

               signupTime = rs.getTimestamp("signup_time") ;

        }

The Timestamp class is a subclass of java.util.Date. However there is not quite a match between how java.util.Date and java.sql.Timetamp store the time. This means that a Timestamp object should not be used where Date objects are required in functions. As the API says “The inheritance relationship between Timestamp and java.util.Date really denotes implementation inheritance, and not type inheritance.”

However it does mean that the standard Date API methods can be used to do things with the Timestamp object, meaning there is a set of standard methods to manipulate and access the values with.

Selecting MySQL Rows with Timestamp Column in a date range from a Java Program

You have a MySQL database table with a Timestamp column, and you need to select a range of dates in which you need to select entries from.

As ever a SELECT statement with a WHERE clause is needed. The following will give you a range of dates from the first to the last. Note that without times specified it is the start of each day. So although 2008-08-31 is specifed no entries on that date will actually be included in the returned results.

String sqlWhereDate =
                " order_placed BETWEEN \”1995-05-01\” AND \”2008-08-31\” ” ;

String sql = “SELECT * FROM orders WHERE ” + sqlWhereDate ;