Date in postgresql

Question

How to insert java Date object into Postgresql DATE cell?

When I do it my way, it's wrong:

Calendar date = Calendar.getInstance(); // wrong
Date date = new Date(); // wrong         

String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE +
                                "VALUES (" + date + ");";
stat.executeUpdate(addRecord)

I have no more ideas...

PS:

public static void CreatePacjenciTable()
{
    try
    {
        Connection conn = DataBase.Connect();

        try
        {
            Statement stat = conn.createStatement();

            String createTable = "CREATE TABLE " + GlobalFields.PACJENCI_TABLE 
                + "(dataUrodzenia DATE);";

            stat.executeUpdate(createTable);
        }
        finally
        {
            conn.close();
        }
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    } 
}

This is where SQL injection starts... That's so 20th century, don't make the mistakes others made before you, learn from them. — Frank Heikens, Aug 29 '12 at 17:57
possible duplicate of [Java Date - Insert into database](http://stackoverflow.com/questions/1081234/java-date-insert-into-database) — Basil Bourque, Jul 03 '15 at 19:18
Java SE 8 (JDBC 4.2) onwards, you should never use the legacy Date-Time API. Check [this answer](https://stackoverflow.com/a/67752047/10819573) and [this answer](https://stackoverflow.com/a/67505173/10819573) to learn how to use `java.time` API with JDBC. — Arvind Kumar Avinash, Jun 20 '21 at 17:35

score 5 · Accepted Answer · answered Aug 29 '12 at 16:53

You shouldn't do it like that at all!

Never, ever, construct SQL strings with their parameters in them. Instead, create a PreparedStatement and set the parameters on it. Like this:

    String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE + "VALUES (?);";
    PreparedStatement stmt = conn.prepareStatement(addRecord);
    stmt.setDate(1, new Date(System.currentTimeMillis()));
    stmt.executeUpdate();

Note that the Date there is a java.sql.Date, which is a subclass of the normal java.util.Date in which the time of day is always midnight UTC; it is used to represent an actual date, not a moment in time. You can alternatively use a java.sql.Timestamp, which represents a moment in time.

score 4 · Answer 2 · answered Aug 29 '12 at 16:49

This is the problem:

String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE +
                   "VALUES (" + date + ");";

You're converting the Date into a string and including that within the SQL. Don't do that. There's no need to use a string conversion here at all.

Instead, use a PreparedStatement and insert the value as a java.sql.Date or possibly a java.sql.Timestamp via a parameter.

You should almost always avoid including values in your SQL as strings - use parameterized SQL instead, and set the values in the statement. This keeps your SQL (code) separate from the parameters (data), avoids the risk of data conversion issues, and also avoids SQL injection attacks.

score 1 · Answer 3 · answered Aug 29 '12 at 16:52

Instead of creating the SQL by concatenating strings (which is the problem here, as Java's string representation of date is surely not understood by Postgres), use PreparedStatement and pass the date as a parameter. Aside of other advantages (pre-compiled statements, ...), it'll allow you to pass the date as Date and let JDBC and the underlying database driver do all the necesary data conversion work for you.

Date in postgresql

3 Answers3

Linked