在雪花中转换日期 [英] To convert date in Snowflake

问题描述

我正在尝试处理日期(准确地说是时间戳)，并且遇到麻烦了.我的要求如下.

I am trying to work with the date (timestamp to be precise) and getting into trouble. My requirement is below.

我有一个阶段表，其中的数据以JSON形式存储在Variant列中，并且数据看起来像在该列中的下面.

I have a stage table where the data is stored as JSON in a Variant Column and the data looks like below in that column.

{
"message_body": {
"campus_code": "TEST",
"campus_name": "TEST",
"event_type": "TEST",
"location_code": "A00000",
"location_name": "TEST",
"order": {
"credit_total": 0,
"app_version": "1.0.9",
"asap": 1,
"order_datetime": "2021-01-08 18:19:34"
}
"timezone_offset_minutes": -360,
}
}

我有以下要求.

将Datetime转换为仅日期，因此我尝试了以下操作及其失败

Convert the Datetime into only date, so i tried the below and its failing

select TO_TIMESTAMP((body:message_body:order:order_datetime), ‘yyyy-mm-dd HH24:MI:SS’) 
FROM "stage_table"

.其失败并显示以下错误消息

. its failing with below error message

SQL compilation error: error line 1 at position 7 too many arguments for function [TO_TIMESTAMP(GET(GET(GET(stage_table.BODY, ‘message_body’), ‘order’), ‘order_datetime’), ‘yyyy-mm-dd HH24:MI:SS’)] expected 1, got 2

我必须从order_datetime中减去分钟.我必须从中减去timezone_offset_minutesorder_datetime

I have to subtract the minutes from order_datetime. I have to subtract timezone_offset_minutes from order_datetime

select datetime, dateadd(minute, -300, body:message_body:order:complete_datetime) 
FROM
"stage_table"

.其失败并显示以下错误消息

. its failing with below error message

时间戳"无法识别

任何快速帮助都将受到高度赞赏.

Any quick help is hugely appreciated.

谢谢

推荐答案

如果您将提取的 order_datetime 转换为带有 :: string :

This will work if you cast the extracted order_datetime to string with ::string:

with sample_table as ( 
select parse_json(replace(replace('{
"message_body": {
"campus_code": "TEST",
"campus_name": "TEST",
"event_type": "TEST",
"location_code": "A00000",
"location_name": "TEST",
"order": {
"credit_total": 0,
"app_version": "1.0.9",
"asap": 1,
"order_datetime": "2021-01-08 18:19:34"
}
,
"timezone_offset_minutes": -360,
}
}', '"', '"'), '"', '"')) body
)

select TO_TIMESTAMP(body:message_body:order:order_datetime::string, 'yyyy-mm-dd HH24:MI:SS')
from sample_table

-- 2021-01-08T18:19:34Z

这篇关于在雪花中转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！