CODEWITHSUNDEEP
mysqlsqlgoogle-bigquery
beenuntz
beenuntz

Reputation: 85

How can I extract minute from time in BigQuery?

How can I extract hour from timestamp/time column in BigQuery?

Timestamp Column sample value: 4/1/2020 12:57:55

Time Column sample value: 12:57:55

I have tried using 

    EXTRACT(minute from Timestamp)
    split(Time,'')[ordinal(1)]

split returns only one value and does not solve my concern. Regular SQL extract does not work.

Upvotes: 0

Views: 5497

Answers (2)

Sabri Karagönen
Sabri Karagönen

Reputation: 2365

As far as I could understand, your datetime column is formatted as string. So you need to parse it first to datetime.

SELECT EXTRACT(minute from PARSE_DATETIME('%e/%m/%Y %H:%M:%S', '4/1/2020 12:57:55'))

For time column, you can use this one:

SELECT EXTRACT(minute from CAST('12:57:55' AS time))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

To extract the hour from the scalar column, you can use:

EXTRACT(hour FROM Timestamp)

EXTRACT() works for both TIMESTAMPs and TIMEs.

Upvotes: 1

Related Questions