PostgreSQL的格式化日期 [英] Formatting Date for Postgresql

问题描述

我有以下

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

和sql函数

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;

PostgreSQL的日期格式为标准（默认）

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：错误： Feb或附近的语法错误

所以我想知道如何解决此问题，我不知道如何正确格式化日期

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

编辑：

我这样调用查询

           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();
            }

所以我认为出现错误是因为格式化日期的方式是错误的并且postgres不会阅读它。

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

推荐答案

听起来好像您是通过将参数直接串联到字符串中来传递参数的。这是一个非常糟糕的主意，因为它可能导致SQL注入。始终使用 PreparedStatement s 和？占位符来传递参数，决不要通过将它们直接连接到查询字符串中来直接传递它们（更多的是，您需要'分隔符）。

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();

或者，PostgreSQL内部日期转换通常是相当好的和灵活的。您可以使用PostgreSQL将字符串参数转换为日期：

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();

这很灵活，但是根据日期格式（您可能无法获得所需的确切结果）可以查看PostgreSQL手册以获取有关日期转换格式的详细信息）。您正在使用的输入格式应该可以正常工作，但是（例如，直接在PostgreSQL中尝试 SELECT CAST（'2012-05-01'AS DATE）），返回正确的PostgreSQL日期。）

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

请注意，使用 new java.sql.Date（cin.getTime（）），您可能会遇到时区问题。您也可以使用 java.sql.Date.valueOf（...）。

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:

这将不起作用，因为日期将是SQL语法本身的一部分，而不是字符串或日期： SELECT * FROM Rooms r不在其中的r.id（从search（ * * cin +， + cout +）中选择*）

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 +"))"

您'd至少需要使用'引号： SELECT * FROM Rooms r其中r.id不在（选择* from search（ '+ cin +'，' + cout +'）） 。在某种程度上，您可以期望参数的格式正确，但是不要这么做。此外，仍然必须使用 CAST（'...'AS DATE）或'...':: DATE来强制转换字符串。

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.

最简单的方法肯定是：

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

（正如a_horse_with_no_name在注释中指出的那样，由于您的内部选择。）

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

这篇关于PostgreSQL的格式化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！