英文:
Is there a way to use TIMESTAMP_WITH_TIMEZONE as a Function input in Oracle
问题
I am trying to create an Oracle SQL Function which accepts a TIMESTAMP_WITH_TIMEZONE value as an input parameter.
CREATE FUNCTION my_func ( timetz IN TIMESTAMP WITH TIMEZONE ) // Not going to work
// code here
BEGIN
// code here
END;
And then try to pass inputs as below. I am trying to set a different timezone provided by the user as a ZonedDateTime
value. In the below code, current LocalDateTime
is taken as an example.
public void myFunc( )
{
CallableStatement stmt = con.prepareCall("{call my_func ( ? )}");
stmt.setObject( ZonedDateTime.of( LocalDateTime.now() ), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
stmt.execute();
}
What would be the ideal way to do this.
英文:
I am trying to create an Oracle SQL Function which accepts a TIMESTAMP_WITH_TIMEZONE value as an input parameter.
CREATE FUNCTION my_func ( timetz IN TIMESTAMP WITH TIMEZONE ) // Not going to work
// code here
BEGIN
// code here
END;
And then try to pass inputs as below. I am trying to set a different timezone provided by the user as a ZonedDateTime
value. In the below code, current LocalDateTime
is taken as an example.
public void myFunc( )
{
CallableStatement stmt = con.prepareCall("{call my_func ( ? )}");
stmt.setObject( ZonedDateTime.of( LocalDateTime.now() ), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
stmt.execute();
}
What would be the ideal way to do this.
答案1
得分: 0
Figured out a way to do that. In case, anyone needs help with this question, I will post the work around I used.
-
将带有时区的日期作为VARCHAR2传递给函数
创建函数my_func (timetz IN VARCHAR2)
// 在此处插入代码
BEGIN
// 使用方法
TO_TIMESTAMP_TZ(timetz, 'YYYY-MM-DD HH24:MI:SS TZR')
END; -
从Java中将带有时区的时间戳作为字符串传递
SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.format(new Timestamp()) + " " + "Pacific/US";stmt.setString(1, timestampTZString);
英文:
Figured out a way to do that. In case, anyone needs help with this question, I will post the work around I used.
-
Pass the date with timezone as a VARCHAR2 to the Function
CREATE FUNCTION my_func ( timetz IN VARCHAR2 )
// code here
BEGIN
// usage
TO_TIMESTAMP_TZ( timetz, 'YYYY-MM-DD HH24:MI:SS TZR')
END; -
Pass the Timestamp with timezone as a String from java
SimepleDateFormatter sdf = new SimpleDateFormatter('yyyy-mm-dd HH:mm:ss')
sdf.format( new Timestamp() ) + " " + "Pacific/US"stmt.setString( 1, timestampTZString );
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论