Formatting Date for Postgresql

Question

I have the following

DateFormat dformat = new SimpleDateFormat("yyyy-M-d");
            dformat.setLenient(false);
            Date cin = dformat.parse(cinDate);

and the sql function

create or replace function search(_checkIn date, _checkOut date) returns setof Bookings as $$
declare
    r Bookings;
begin
    for r in
    select * from Bookings
    loop
        if ((_checkIn between r.checkIn and r.checkOut) or (_checkOut between r.checkIn and r.checkOut)) then
            return next r;
        end if;
    end loop;
    return;
end;
$$ language plpgsql;

The date format for the postgresql is standard (default)

create table Bookings (
    id          serial,
    status      bookingStatus not null,
    pricePaid   money not null,
    firstName   text,
    lastName    text,
    address     text,
    creditCard  text,
    checkOut    date not null,
    checkIn     date not null,
    room        integer not null,
    extraBed    boolean not null default false,

    foreign key (room) references Rooms(id),
    primary key (id)
);

and I'm trying to parse a date into the function so it can return a table for me, I seem to run into the issue of date formatting (which is why I think I'm getting this error)

org.postgresql.util.PSQLException: ERROR: syntax error at or near "Feb"

So I was wondering how would I fix this problem, I don't know how to format the date properly

EDIT:

I'm calling the query like this

           try {
                String searchQuery = "SELECT * FROM Rooms r where r.id not in (select * from search(" + cin +", " + cout +"))";
                PreparedStatement ps = conn.prepareStatement(searchQuery);
                rs = ps.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }

so I think the error comes in because the way I format the date is wrong and postgres won't read it

Answer 1

It sounds like you're passing the argument by concatenating them directly into the string. This is a very bad idea, since it can lead to SQL injections. Always use PreparedStatements with the ? place-holders to pass parameters, never pass them directly by concatening them directly into the query string (more so, you'd need the ' delimiters around).

You could have something like:

 PreparedStatement stmt
     = con.prepareStatement("SELECT id FROM Bookings WHERE checkIn=?")
 stmt.setDate(1, new java.sql.Date(cin.getTime()));
      // ? parameters are indexed from 1
 ResultSet results = stmt.executeQuery();

Alternatively, PostgreSQL internal date conversion is usually fairly good and flexible. You could cast the string parameter to a date with PostgreSQL:

 PreparedStatement stmt
     = con.prepareStatement("SELECT id FROM Bookings WHERE checkIn=CAST(? AS DATE)");
 stmt.setString(1, cinDate);
 ResultSet results = stmt.executeQuery();

This is flexible, but might not lead to the exact result you need depending on the date format (you can check the PostgreSQL manual for details on date conversion formats). The input format you're using should work just fine, though (Try SELECT CAST('2012-05-01' AS DATE) directly in PostgreSQL, for example, this will return a correct PostgreSQL date.)

Note that when using new java.sql.Date(cin.getTime()), you're likely to run into time zone issues. You could use java.sql.Date.valueOf(...) too.

To clarify, following your edit:

This will not work, since the dates would be part of the SQL syntax itself, not strings or dates: "SELECT * FROM Rooms r where r.id not in (select * from search(" + cin +", " + cout +"))"

You'd at least need to use ' quotes: "SELECT * FROM Rooms r where r.id not in (select * from search("' + cin +"', '" + cout +"'))". Here, to a degree, you could expect the parameters to be formatted properly, but don't do it. In addition, would would still have to cast the string using CAST('...' AS DATE) or '...'::DATE.

The simplest way would certainly be:

String searchQuery = "SELECT * FROM Rooms r where r.id not in (select SOMETHING from search(CAST(? AS DATE), CAST(? AS DATE)))";
PreparedStatement ps = conn.prepareStatement(searchQuery);
ps.setString(1, cinDate);
ps.setString(2, coutDate);

(As a_horse_with_no_name pointed out in a comment, the general query wouldn't work anyway because of your inner select.)

Answer 2

You already have advice concerning prepared statements and proper format.

You can also largely simplify your PostgreSQL function:

CREATE OR REPLACE FUNCTION search(_checkin date, _checkout date)
  RETURNS SETOF bookings AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT *
    FROM   bookings
    WHERE  _checkin BETWEEN checkin AND checkout
       OR  _checkiut BETWEEN checkin AND checkout;

END;
$BODY$ language plpgsql;

Or even:

CREATE OR REPLACE FUNCTION search(_checkin date, _checkout date)
  RETURNS SETOF bookings AS
$BODY$
    SELECT *
    FROM   bookings
    WHERE  _checkin BETWEEN checkin AND checkout
       OR  _checkiut BETWEEN checkin AND checkout;
$BODY$ language sql;

Rewrite the LOOP plus conditions to a plain SQL statement which is much faster.

• Return from a plpgsql function with RETURN QUERY - simpler and faster than looping.
• Or use a plain sql function.

Either variant has its advantages.

• No point in using mixed case identifiers without double quoting. Use all lower case instead.

Answer 3

According to this page, the standard format for date/time strings in SQL is:

YYYY-MM-DD HH:MM:SS

And of course for dates you can use

YYYY-MM-DD

PostgreSQL accepts other formats (see here for some details) but there's no reason not to stick to the standard.

However, since you are getting a syntax error it sounds like you are injecting the date strings into your SQL statement without the proper quoting/escaping. Double-check that you are properly escaping your input.