雪花中的时间戳转换出错

Question

我是雪花的新手。我在ETL过程中使用了一个控制表。我想更新控制表的watermark_value字段中的当前时间戳。下面是我的程序代码

CREATE OR REPLACE PROCEDURE test_sp()
RETURNS string
LANGUAGE javascript
EXECUTE AS CALLER
AS
$$  

var result = "";  
snowflake.execute( {sqlText: "BEGIN TRANSACTION;"} );  

try {   

var max_sync_sql_command = `SELECT watermark_value,dateadd(minute, -1, sysdate()) as new_watermark_value  
from APPLICATION_CONTROL 
WHERE TABLE_NAME = 'TEST1'; `

var max_sync_time_result = snowflake.execute ({ sqlText: max_sync_sql_command });

max_sync_time_result.next();            
var max_sync_time = max_sync_time_result.getColumnValue(1);  
var new_watermark_value = (max_sync_time_result.getColumnValue(2));  

/*Some ETL Code Here*/ 
var update_watermark_sql_command = `update APPLICATION_CONTROL  
set watermark_value =  to_varchar(  to_timestamp( '`+new_watermark_value+`', 'DY MON DD HH24:MI:SS TZD YYYY') , 'YYYY-MM-DD HH24:MI:SS')
WHERE TABLE_NAME = 'TEST1'; `
snowflake.execute ({ sqlText: update_watermark_sql_command });

snowflake.execute( {sqlText: "COMMIT;"} );
result = "Record updated Successfully"
}
catch (err)  
{
snowflake.execute( {sqlText: "ROLLBACK;"} );
result =  "Failed: Code: " + err.code + "n  State: " + err.state;
result += "n  Message: " + err.message;
result += "nStack Trace:n" + err.stackTraceTxt;
}

return result;    

$$;

我在程序执行过程中出现以下错误

Failed: Code: 100183
State: P0000
Message: Can't parse 'Fri Jun 11 2021 12:47:38 GMT-0700 (Pacific Daylight Time)' as timestamp with format 'DY MON DD YYYY HH24:MI:SS TZD'
Stack Trace:
At Snowflake.execute, line 25 position 12

application_control表中watermark_value列的数据类型为TIMESTAMP_NTZ。

任何帮助都将不胜感激。提前谢谢。

Answer 1

从查询中获取结果时，列的顺序错误

var max_sync_time = max_sync_time_result.getColumnValue(1);  
var new_watermark_value = (max_sync_time_result.getColumnValue(2)); 

应该是

var max_sync_time = max_sync_time_result.getColumnValue(2);  
var new_watermark_value = (max_sync_time_result.getColumnValue(1)); 

更新

经过一些研究，Snowflake处理日期/时间的方式似乎有点奇怪，当将值从SQL传输到Javascript并再次传输时。有提到SFDATE类型，但在我看来，这并没有很好的记录。

不管怎样，下面的代码结构应该是有效的——尽管为什么它有效以及你尝试的是什么都不是任何人的猜测：

var update_watermark_sql_command = `update APPLICATION_CONTROL  
set watermark_value =  :1 
WHERE TABLE_NAME = 'TEST1'; `
snowflake.execute ({ sqlText: update_watermark_sql_command, binds: [new_watermark_value.toISOString()] });

Answer 2

在获取值时，我已经将时间戳转换为字符(用于_char函数(，并且它起作用了。

var max_sync_sql_command = `SELECT watermark_value,to_char(dateadd(minute, -1, sysdate())) as new_watermark_value  
from APPLICATION_CONTROL 
WHERE TABLE_NAME = 'TEST1'; `