英文:
Insert time in Oracle SQL using GregorianCalendar in java
问题
以下是翻译好的部分：
在我的Oracle SQL表中，我有一个列默认为CURRENT_TIMESTAMP，我相信它返回带有时区的时间戳。我想通过使用GregorianCalendar对象来更新这一列。我尝试了以下代码以查看它返回了什么：
GregorianCalendar date = new GregorianCalendar();
System.out.println("日期是：" + date.getTime());
date.getTime() 返回 Thu Aug 27 19:50:34 EDT 2020，但我不确定这是否是正确的格式。我相信它应该类似于 1960-01-01 23:03:20 的格式。
什么是正确的格式来更新时间戳列，以及如何使用GregorianCalendar对象创建具有Oracle SQL时间戳类型正确格式的时间戳？
英文:
In my oracle SQL table, I have a column that is default to CURRENT_TIMESTAMP, which I believe returns timestamp with timezone. I want to update this column by using GregorianCalendar object. I tried this to see what it returns:
GregorianCalendar date = new GregorianCalendar();
System.out.println(" date is: " + date.getTime());
date.getTime() returns Thu Aug 27 19:50:34 EDT 2020, but I am not sure if this is right format. I believe it is supposed to be something like 1960-01-01 23:03:20
What is the right format to update timestamp column and how do I use GregorianCalendar object to create timestamp that has the right format for Oracle SQL timestamp type?
答案1
得分: 2
你可以从你的GregorianCalendar获取一个ZonedDateTime，并将其直接用作PreparedStatement的参数：
GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT-8"));
ZonedDateTime zdt = cal.toZonedDateTime();
pstmt = con.prepareStatement("update the_table set the_column = ? where id = ?");
pstmt.setObject(1, zdt);
pstmt.setInt(2, 42);
pstmt.executeUpdate();
英文:
You can get a ZonedDateTime from your GregorianCalendar and use that directly as parameter to a PreparedStatement:
GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT-8"));
ZonedDateTime zdt = cal.toZonedDateTime();
pstmt = con.prepareStatement("update the_table set the_column = ? where id = ?");
pstmt.setObject(1, zdt);
pstmt.setInt(2, 42);
pstmt.executeUpdate();
答案2
得分: 1
以下是翻译好的部分：
该函数 current_timestamp 返回数据类型 TIMESTAMP WITH TIME ZONE，所以您的问题不是时间戳的格式，而是要找到适当的类，即
oracle.sql.TIMESTAMPTZ
相应的 setter 是
下面的代码首先将日历转换为 java.sql.Timestamp，然后再转换为 oracle.sql.TIMESTAMPTZ
请注意，current_timestamp 使用连接会话的时区，所以如果您想要实现相同的逻辑，可以在 GregorianCalendar 的构造函数中添加相关的时区：
GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT-8"));
Timestamp tst = new Timestamp(cal.getTimeInMillis());
def stmt = con.prepareStatement("update tab set curr_tst = ?");
stmt.setTIMESTAMPTZ(1, new oracle.sql.TIMESTAMPTZ(con, tst, cal));
stmt.executeUpdate();
英文:
The function current_timestamp returns the data type TIMESTAMP WITH TIME ZONE, so your problem is not the format of the timestamp, but to find the proper class which is
oracle.sql.TIMESTAMPTZ
and the corresponding setter is
Snipped below converts first the calendar to the java.sql.Timestamp and than to oracle.sql.TIMESTAMPTZ
Note that the current_timestamp uses the time zone of the connected session, so if you want to implement the same logic, you can add the relevant time zone in the constructor of the GregorianCalendar
GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT-8"))
Timestamp tst = new Timestamp(cal.getTimeInMillis())
def stmt = con.prepareStatement("update tab set curr_tst = ?")
stmt.setTIMESTAMPTZ(1,new oracle.sql.TIMESTAMPTZ(con,tst,cal))
stmt.executeUpdate()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论