Search code examples
sqlgoogle-bigqueryisodate

BigQuery: Format ISO Date

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'

Solution

  • You need to include those ISO symbols into format specifier as constants:

    select parse_timestamp('%FT%TZ', '2021-04-12T17:38:10Z')

|           f0_           |
---------------------------
| 2021-04-12 17:38:10 UTC |

    UPD: If you have fractional seconds, you can include optional milliseconds element %E*S instead of time element %T. For non-UTC timestamps there should also be timezone element: %Ez. So, the possible solution could be:

    with a as (
    select '2021-04-12T20:44:06.95841Z' as ts_str union all
    select '2021-04-12T23:44:07.83738+03:00' union all
    select '2021-04-12T23:44:08+03:00'
)
select parse_timestamp('%FT%H:%M:%E*S%Ez', regexp_replace(ts_str, 'Z$', '+00:00')) as ts
from a

|               ts               |
|--------------------------------|
| 2021-04-12 20:44:06.958410 UTC |
| 2021-04-12 20:44:07.837380 UTC |
| 2021-04-12 20:44:08 UTC        |