postgresql 在 Postgres 表中插入时间戳问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16520767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Issue inserting timestamp in Postgres table
提问by java_enthu
I am trying to insert a value in the postgres table through Java . Column type is timestamp.
我正在尝试通过 Java 在 postgres 表中插入一个值。列类型是时间戳。
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.
错误：格式字符串中“mm”字段的值存在冲突。详细信息：此值与相同字段类型的先前设置相矛盾。
I am not sure what is going wrong !!
我不确定出了什么问题！！
Any help on this will be useful. Thanks in advance. -JE
对此的任何帮助都会有所帮助。提前致谢。-乙脑
回答by a_horse_with_no_name
mmis alwaysthe month for the to_date()function. There is no difference between mmand MM(unlike in Java's SimpleDateFormat).
mm是始终为月to_date()功能。mm和之间没有区别MM（与 Java 的 SimpleDateFormat 不同）。
You need to use mifor the minutes.
您需要使用mi分钟。
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
手册中提供了所有模式的完整列表：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.Timestampand setTimestamp()instead. That relief you from any formatting problems and protect you against SQL injection.
但是您不应该首先使用“动态”SQL。最好使用PreparedStatement,java.sql.Timestamp而setTimestamp()不是。这使您免于任何格式问题并保护您免受 SQL 注入。
回答by Skillcoil
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.
尝试从查询中拆分日期部分并尝试比较这些值。看来（至少从我看来）代表分钟的 mm 不符合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.
如果您将这部分拉到查询之外，您可以检查值，也许您会发现问题所在。
回答by KhAn SaAb
do like this.
这样做。
java.sql.Date date=new Date();
Timestamp timestamp = new Timestamp(date.getTime());
this.date = timestamp;
Then add this.dateinto database..
然后加入this.date数据库..
回答by heronsanches
try it:ps.setTimestamp(position, new Timestamp(System.currentTimeMillis()));
试试看：ps.setTimestamp(position, new Timestamp(System.currentTimeMillis()));
回答by emecas
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());
