Search code examples
google-bigqueryintervals

How to get total number of minutes in an INTERVAL in BigQuery?

After subtracting 2 dates (ended_at - created_at) I have a time interval like 0-0 0 1:8:23.810192. How do I get total number of minutes in the interval which is 68 in this example?

Solution

  • DATE_DIFF does not support MINUTE granularity.

    But based on your result values of time: "1:8:23.810192" I am assuming that the dates you have (ended_at and created_at) are actually stored as timestamps.

    In that case, you can just use timestamp_diff function and get the minutes directly like this: Example:

    select timestamp_diff(current_timestamp(), timestamp_sub(current_timestamp(), INTERVAL 1 DAY), MINUTE)

    => 1440

    For your use case: it will be: select timestamp_diff(ended_at, created_at, MINUTE)

    Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_diff