BigQuery: extract SECOND from TIMESTAMP

Question

How can i run this query?

Error Message: No matching signature for function EXTRACT for argument types: DATE_TIME_PART FROM INT64. Supported signatures: EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); EXTRACT(DATE_TIME_PART FROM DATETIME); EXTRACT(DATE_TIME_PART FROM TIME) at [12:12]

They both give the same error message

 WHERE EXTRACT( SECOND FROM event_timestamp )
             - EXTRACT( SECOND FROM last_event) >= (60 * 10)
        OR last_event IS NULL

 WHERE EXTRACT( SECOND FROM event_timestamp AT TIME ZONE "UTC")
             - EXTRACT( SECOND FROM last_event  AT TIME ZONE "UTC") >= (60 * 10)
        OR last_event IS NULL

Answer 1

use TIMESTAMP_MICROS()

WHERE EXTRACT( SECOND FROM TIMESTAMP_MICROS(event_timestamp))
             - EXTRACT( SECOND FROM last_event) >= (60 * 10)
        OR last_event IS NULL

Answer 2

If you want events that are more than 10 minutes from the previous timestamp, just use some arithmetic and comparisons:

where event_timestamp > last_event + (60 * 10 * 1000000) or
      last_event is null

You are storing the timestamp as a microseconds value. You don't need to convert to another type.

If you really wanted to convert this to timestamp values, you could use:

where timestamp_micros(event_timestamp) > timestamp_add(timestamp_micros(last_event), interval 10 minute) or
      last_event is null

In particular, you don't want to extract seconds. That value is always going to be between 0 and 59.