39

How can I get the difference in minutes between 2 timestamp fields in google bigquery? The only function I know is Datediff which gives the difference in day

Thanks

user3569267
  • 1,065
  • 3
  • 14
  • 27
  • 1
    Does anyone have the answer for DATETIME fields? – Praxiteles Sep 05 '17 at 06:51
  • 2
    You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work#5235 – Pentium10 Oct 18 '17 at 11:53

3 Answers3

57

Pentium10's answer works for Legacy SQL. If you're using Standard SQL you can use TIMESTAMP_DIFF and it will do the math for you:

TIMESTAMP_DIFF(timestamp_1, timestamp_2, MINUTE)

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-functions

Paul Bendevis
  • 2,381
  • 2
  • 31
  • 42
11

Use TIMESTAMP_TO_USEC(<timestamp>) and do the math.

https://developers.google.com/bigquery/query-reference#datetimefunctions

Pentium10
  • 204,586
  • 122
  • 423
  • 502
-2

DATETIME_DIFF(datetime_1, datetime_2, MINUTE/SECOND)

amit gorain
  • 1