Issue inserting timestamp in Postgres table

Question

I am trying to insert a value in the postgres table through Java . Column type is timestamp.

The code is like this :

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
String gameStartedTime = format.format(new Date());

String query= "UPDATE gameStatus g SET g.status ='" + gameStatus
    + g.gameStartTime= to_date('"
            + gameStartedTime  + "','yyyy-MM-dd HH:mm:ss')"
    // Doesn't matter much
+ " WHERE g.status = 'STARTED' AND " + "g.condition="+ game.getCondition();

Now when I try to execute this statement it fails I get the message like this :

ERROR: conflicting values for "mm" field in formatting string. DETAIL: This value contradicts a previous setting for the same field type.

I am not sure what is going wrong !!

Any help on this will be useful. Thanks in advance. -JE

Answer 1

mm is always the month for the to_date() function. There is no difference between mm and MM (unlike in Java's SimpleDateFormat).

You need to use mi for the minutes.

A full list of all patterns is available in the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

But you shouldn't use "dynamic" SQL in the first place. It's better to use a PreparedStatement, java.sql.Timestamp and setTimestamp() instead. That relief you from any formatting problems and protect you against SQL injection.