Select 7 days ago JDBC

Question

Im creating a JDBC to a restaurant database program where an user may see the report of the last 7 days profit.

I dont know how to select the last seven days, so the program shows the total profit from seven days ago.

I would like to know how i can achieve this, my code would look something like this:

   public static void showSevenDaysAgoSells() {
            double value = 0.0;
            try {       
                System.out.println("----Showing last seven days profit----");
                PreparedStatement stmt = conn.prepareStatement("SELECT sum(total) FROM sells WHERE date > date_from_7_days_ago");
                java.sql.Date date= new java.sql.Date(new java.util.Date().getTime());
                stmt.setObject(1, LocalDate.now(ZoneId.of("America/Caracas")).minusWeeks(1));               
                stmt.executeQuery();
                ResultSet rs = stmt.executeQuery();
                rs.next();
                String sum = rs.getString(1);
                value = Double.parseDouble(sum);
                System.out.println("DAYS" + "\t" +  "TOTAL");
                System.out.println(date+ "\t" + sum);
                } catch(Exception e) {
                e.printStackTrace();
            }
        }

it gives me this error: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0. — Cesar, Apr 24 '17 at 03:43
By the way i saw the post you linked here before posting this, it did not work for me — Cesar, Apr 24 '17 at 03:43
Update your question and show the actual code which caused this error. — Tim Biegeleisen, Apr 24 '17 at 03:47
Well of course this would fail, as after your update the prepared statement has no `?` placeholders. Use `WHERE date = ?` and it should start working again. — Tim Biegeleisen, Apr 24 '17 at 03:52
If you are using Java 8 and the latest JDBC drivers you can use `setObject(1, LocalDate.now().minusDays(7))` for setting it and `getObject(1, LocalDate.class)` for retrieving dates. It's better not to rely on `java.util.Date` and it's subclasses when you can avoid it. — coladict, Apr 24 '17 at 07:08

score 3 · Accepted Answer · edited May 23 '17 at 12:34

tl;dr

PreparedStatement stmt = conn.prepareStatement(
    "SELECT sum( total_ ) FROM sells_ WHERE date_ > ? ;"
);

…and…

stmt.setObject(                            // Use JDBC 4.2 method for passing/fetching java.time types.
    1 ,                                    // Specify which placeholder `?` in PreparedStatement (1, 2, 3, etc.). In this case, `1`. 
    LocalDate.now(                         // Get current date (today) in desired/expected time zone. 
        ZoneId.of( "America/Montreal" )
    ).minusWeeks( 1 )                      // Go back in time a week (7 days).
)

`?`

You embedded your variable date_from_7_days_ago within your SQL string. So it is treated as a string literal rather than as a Java variable’s passed value. Replace that variable name with the question mark ? placeholder for the PreparedStatement.

PreparedStatement stmt = conn.prepareStatement( "SELECT sum( total_ ) FROM sells_ WHERE date_ > ? ;" );

Tip: Avoid naming your columns and other database identifiers with a reserved words such as date and total. Various SQL databases in total reserve over a thousand reserved words, so a collision is easier than people realize. The SQL spec explicitly promises to never reserve any word with a trailing underscore. So name all your identifiers date_, total_, and such to never worry about collisions.

Avoid legacy date-time classes

Both the Question and the other Answer use the troublesome old date-time classes that have proven to be poorly designed, confusing, and flawed. They are now legacy, supplanted by the java.time classes.

The java.time.LocalDate class represents a date-only value without time-of-day and without time zone.

Time Zone

Both the Question and other Answer ignore the crucial issue of time zone.

A time zone is necessary in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "America/Montreal" );
LocalDate today = LocalDate.now( z );

If your business logic requirements dictate UTC rather than a particular time zone, use the constant ZoneOffset.UTC.

LocalDate today = LocalDate.now( ZoneOffset.UTC ) ;

To get seven days ago (a week), let the object do the date-math.

LocalDate weekAgo = LocalDate.minusWeeks( 1 );

JDBC

If your JDBC driver complies with JDBC 4.2 and later, you can directly use the java.time types via PreparedStatement.setObject and ResultSet.getObject.

myPreparedStatement.setObject( … , weekAgo );

and retrieve:

LocalDate ld = myResultSet.getObject( … , LocalDate.class );

At least these two JDBC drivers for Postgres may have been updated for JDBC 4.2 though I have not yet tested them for support of java.time types.

If your JDBC driver does not comply, then fall back to using java.sql. But do so as briefly as possible, converting to/from java.time immediately. To convert, look to new methods added to the old classes. In this case we would have to fall back to using java.sql.Date using the valueOf and toLocalDate methods.

myPreparedStatement.setObject( … , java.sql.Date.valueOf( weekAgo ) );

and retrieve:

LocalDate ld = myResultSet.getDate( … ).toLocalDate() ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
- See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

it gives me the following error: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.LocalDate. Use setObject() with an explicit Types value to specify the type to use. — Cesar, Apr 24 '17 at 13:14

score -1 · Answer 2 · answered Apr 24 '17 at 06:52

Do

    final long OneDayMillis = 86400000l;
    PreparedStatement stmt = conn.prepareStatement("SELECT sum(total) FROM sells WHERE date > ?");
    java.sql.Date date = new java.sql.Date(System.currentTimeMillis()-OneDayMillis*7l);
    stmt.setDate(1, date);

Take into account that the current date of your server might be different from the current date of your client machine, therefore it may be better to use server-side date with a SQL statement like

SELECT sum(total) FROM sells WHERE date > current_date - integer '7'

without any parameter binding from client side.

Doing milliseconds calculations like that without `java.util.Calendar` can become a problem around DST changes. I know it's an edge case, but that's why you should use it. — coladict, Apr 24 '17 at 07:00