Java 如何在数据库中插入时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21119096/
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
how to insert Timestamp in database?
提问by Shakti
I am getting this error:
我收到此错误：
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
java.lang.IllegalArgumentException：时间戳格式必须为 yyyy-mm-dd hh:mm:ss[.fffffffff]
Here is my java code:
这是我的Java代码：
<% int reg;
String sdate = request.getParameter("sdate");
//Timestamp ts=request.get
//out.println(sdate);
String text = "0000-00-00 00:00:00";
Timestamp ts = Timestamp.valueOf(text);
// Timestamp ts1=Timestamp.valueOf(sdate);
// Timestamp ts2=Timestamp.valueOf(edate);
try{
String s;
Format formatter;
Date date = new Date(sdate);
//out.println(date);
formatter = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");
s = formatter.format(date);
out.println(s);
PreparedStatement ps=cn.prepareStatement("insert into c1 values(?)");
// ps.setString(1,ts);
ps.setTimestamp(1,ts);
int i=ps.executeUpdate();
if(i==1)
{
pw.println("record inserted");
}
else
{
pw.println("not iNSERTED");
}
//cn.close();
}
catch(Exception e)
{
out.println(e.toString());
}
%>
Any Suggestions?
有什么建议？
采纳答案by Pavan
Shakti, you need to address the error. java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
Shakti，你需要解决这个错误。 java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
and then have a look at the way you've formatted your date. You'll need to change your line below
然后看看你格式化日期的方式。您需要更改下面的行
formatter = new SimpleDateFormat("yyyy:MM:dd HH:mm:ss");
to:
到：
formatter = SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Notice the difference and use of the hypen character in the date instead of the colon character which youre using.
请注意日期中连字符而不是您使用的冒号字符的区别和使用。
UPDATE 1
更新 1
Ok. so after @sotirios' comment, I decided to do some extra research and I came across this postand learned something. You cant give a 0000-00... date configuration because MySQL [uses "zero dates" as special placeholder
好的。所以在@sotirios 发表评论后，我决定做一些额外的研究，我看到了这篇文章并学到了一些东西。您不能给出 0000-00... 日期配置，因为 MySQL [使用“零日期”作为特殊占位符
The solution plain and simple is to change the date string from
解决方案简单明了是将日期字符串从
String text = "0000-00-00 00:00:00";
to atleast
至少
String text = "0001-01-01 00:00:00";
And if you really want to send 0000-00-... date strings I believe the solution is to specify "zeroDateTimeBehavior=convertToNull" as a parameter to your MySQL connection (either in datasource URL or as an additional property), e.g.:
如果您真的想发送 0000-00-... 日期字符串，我相信解决方案是将“zeroDateTimeBehavior=convertToNull”指定为 MySQL 连接的参数（在数据源 URL 中或作为附加属性），例如：
jdbc:mysql://localhost/myDatabase?zeroDateTimeBehavior=convertToNull
jdbc:mysql://localhost/myDatabase?zeroDateTimeBehavior=convertToNull
This will cause all such values to be sent as NULLs.
这将导致所有此类值作为 NULL 发送。
回答by Sotirios Delimanolis
This
这个
String text = "0000-00-00 00:00:00";
is not a valid date. There is no month or day with a value of 0.
不是有效日期。没有值为 的月或日0。
The Timestamp.valueOf(text)will not be able to parse it.
该Timestamp.valueOf(text)将无法解析它。
At least use
至少使用
String text = "0000-01-01 00:00:00";
回答by Rich
i use the jdbc's native timestamp type:
我使用 jdbc 的原生时间戳类型：
Date date = myobject.getDate();
cstmt = conn.prepareCall("{call dbo.myproc(?)}");
cstmt.setTimestamp("date", new Timestamp(date.getTime())
cstmt.execute();
