I'm trying to parse a timestamp which is in ISO Date 8601 format.
Example: 2021-04-10T14:11:00Z
This information is stored inside a JSON object and for that reason I'm extracting that data as a string:
The format I'm looking for is a yy-MM-dd hh:mm format and for that I've tried the following
SQL CODE
SELECT document_id,
json_extract(data, '$.Pair') as pair,
PARSE_TIMESTAMP('%y-%m-%d %H:%M', json_extract(data, '$.AlertTime')) as alerttime,
COUNT(document_id) as alert_count
FROM `tradingview-alerts-26eb8.alltables.TradingView_000_raw_latest` as alert_view
GROUP BY alerttime, document_id, pair
Errors
The code from above causes the following error:
Failed to parse input string '"2021-04-10T03:17:00Z"
The reason for this is the T in the middle of the date, I believe,
In order to discard that I tried this change:
SUBSTR(json_extract(data, '$.AlertTime'), 1, 10))
But with that I'm getting an error on a different row:
Failed to parse input string '"2021-04-1'
I'm wondering if it is because of how the date is being presented (year-month-date) the date not having 2 digits? such as 2021-04-01 instead of 2021-04-1.
However if I try with
SUBSTR(json_extract(data, '$.AlertTime'), 1, 11))
The error I'm getting is
Failed to parse input string '"2021-04-10'