for example i have this
SELECT
JSON_EXTRACT_SCALAR(jsonBlob, '$.userName') as userName,
JSON_EXTRACT_SCALAR(jsonBlob, '$.Id') as id,
JSON_EXTRACT_SCALAR(jsonBlob, '$.CreatedDateTime') as CreatedDateTime,
from dataset.tableName
this gives me back the a result but the CreatedDateTime field is in unix time stamp, i want to get it in 2018-02-01 17:11:43 format. i know i can use
SELECT MSEC_TO_TIMESTAMP(CreatedDateTime) as CreatedDateTime
to convert it later, but what if i want to do this in the above select statement. any idea?
SELECT
MSEC_TO_TIMESTAMP(INTEGER(JSON_EXTRACT_SCALAR(jsonBlob, '$.CreatedDateTime'))) as CreatedDateTime
above is for Legacy SQL as looks like it is what you use in your question
forgot to mention - for BigQuery Standard SQL , the same will look like below
SELECT
TIMESTAMP_MILLIS(CAST(JSON_EXTRACT_SCALAR(jsonBlob, '$.CreatedDateTime') AS INT64)) as CreatedDateTime
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加