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

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 ;