CODEWITHSUNDEEP
javapostgresqljdbc
java_enthu
java_enthu

Reputation: 2337

Issue inserting timestamp in Postgres table

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

Upvotes: 8

Views: 22489

Answers (6)

Basil Bourque
Basil Bourque

Reputation: 340118

Wrong data type for column

Column type is timestamp.

TIMESTAMP WITHOUT TIME ZONE, otherwise known as TIMESTAMP in Postgres, is the wrong data type for recording a moment, a point on the timeline.

👉🏽 To record a moment, you must use the type TIMESTAMP WITH TIME ZONE as it is known both in Postgres and the SQL standard.

The difference between the two types is:

  • TIMESTAMP WITHOUT TIME ZONE accepts any date and time you pass with no regard for time zone. So with a date-time of January 23, 2025 at 12:00, we have no idea if you meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US — three very different moments, several hours apart.
  • TIMESTAMP WITH TIME ZONE does account for time zone. If you pass only a date and a time, Postgres assumes you meant an offset of zero, and stores the value. If you pass an offset or time zone along with the date & time, Postgres uses that offset/zone to adjust into UTC (offset of zero), and then stores the value. The stored value is always in UTC. (Be aware some middleware/tools will unfortunately apply time zone dynamically after retrieval but before delivery to client!)

Avoid legacy date-time classes

The terribly-flawed date-time classes in Java have been supplanted by the modern java.time classes defined in JSR 310 and built into Java 8+.

👉🏽 Never use Date, Calendar, Timestamp, SimpleDateFormat, etc.

java.time

To capture the current moment in Java, we might use java.time.Instant. An Instant represents the moment as seen with an offset-from-UTC of zero hours-minutes-seconds.

Instant instant = Instant.now() ;  // Current moment as seen in UTC.

java.time.OffsetDateTime

However, while JDBC 4.2 and later requires every JDBC driver to support the java.time classes, Instant is not mapped. Instead, JDBC maps the java.time.OffsetDateTime class to the SQL standard type TIMESTAMP WITH TIME ZONE.

👉🏽 Capture the current moment with OffsetDateTime.now.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Terms: An offset is merely a number of hours-minutes-seconds ahead/behind the temporal meridian of UTC. A time zone is a named (Continent/Region, ex: Europe/Paris) history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians. The authors of the SQL standard did not understand date-time handling well, and misused the term time zone where they really meant offset.

Avoid SQL injection

You said:

String query= "UPDATE gameStatus g SET g.status ='" + gameStatus
    + g.gameStartTime= to_date('"
            + gameStartedTime  + "','yyyy-MM-dd HH:mm:ss')"

👉🏽 Do not concatenate text like this to generate SQL. That bad habit will make you vulnerable to SQL Injection attacks. Instead, use parameters passed into a prepared statement.

Use objects, not text

And use smart objects rather than dumb strings. We have date-time objects in Java mapped to date-time types in SQL & Postgres. So do not convert in and out of text.

Modern Java now offers text blocks to ease the chore of writing SQL.

String sql = """
        UPDATE order_ 
        SET order_.when_ = ? 
        WHERE … 
        ;
        """ ;
…
myPreparedStatement.setObject( 1 , odt ) ;

You asked:

ERROR: conflicting values for "mm" field in formatting string

That issue is moot. You should not be manipulating text while exchanging date-time values between Java and Postgres.

Upvotes: 1

heronsanches
heronsanches

Reputation: 606

try it: ps.setTimestamp(position, new Timestamp(System.currentTimeMillis()));

Upvotes: 2

emecas
emecas

Reputation: 1586

This way works for me using current time:

    String query = "INSERT INTO table1 (id,t) VALUES (?, ?)"; 
    //update table1 set t=? where id=?
    Connection con = null;
    PreparedStatement ps = null;
    try{
        con = dataSource.getConnection();
        ps = con.prepareStatement(query);

        ps.setLong(1, 1234); // update ps.setLong(2, 1234);
        Calendar cal = Calendar.getInstance();  
        Timestamp timestamp = new Timestamp(cal.getTimeInMillis());
        ps.setTimestamp(2,timestamp); // ps.setTimestamp(1,timestamp);
        int out = ps.executeUpdate();
        if(out !=0){
            System.out.println("Record saved");
        }
    }catch(SQLException e){
        e.printStackTrace();
    }finally{
        try {
            ps.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Or, you can establish a specific timestamp by using these lines:

    Calendar cal = Calendar.getInstance();
    cal.set(Calendar.YEAR, 2015); 
    cal.set(Calendar.MONTH, 0); // 0 january
    cal.set(Calendar.DAY_OF_MONTH, 26); 
    cal.set(Calendar.HOUR_OF_DAY, 10); 
    cal.set(Calendar.MINUTE, 47);
    cal.set(Calendar.SECOND, 0);
    cal.set(Calendar.MILLISECOND, 0);
    Timestamp timestamp = new Timestamp(cal.getTimeInMillis());

Upvotes: 1

KhAn SaAb
KhAn SaAb

Reputation: 5376

do like this.

java.sql.Date date=new Date();
Timestamp timestamp = new Timestamp(date.getTime());
this.date = timestamp;

Then add this.date into database..

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 8

Skillcoil
Skillcoil

Reputation: 184

Try to split the date part from the query and try to compare these values. It appears (at least from where I see) that the mm which stand for minutes, does not comply with g.gameStartTime= to_date.

If you pull this part outside the query you can check the values, maybe you will find what the problem is there.

Upvotes: 1

Related Questions