在Oracle中是否有一种方法可以将TIMESTAMP_WITH_TIMEZONE用作函数输入?

huangapple go评论89阅读模式
英文:

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.

  1. 将带有时区的日期作为VARCHAR2传递给函数

    创建函数my_func (timetz IN VARCHAR2)
    // 在此处插入代码
    BEGIN
    // 使用方法
    TO_TIMESTAMP_TZ(timetz, 'YYYY-MM-DD HH24:MI:SS TZR')
    END;

  2. 从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.

  1. 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;

  2. 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 );

huangapple
  • 本文由 发表于 2020年8月10日 21:12:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/63340937.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定