CODEWITHSUNDEEP
mysqlgoogle-bigquery
BT10
BT10

Reputation: 49

How to take a difference between two DATETIME columns in standard SQL?

I am trying to take the difference between two consecutive rows using the DateTime column field. When taking the difference I get an error message saying difference can only be taken for int64, float64 or numeric values.

I tried converting DateTime to String and then to int64/float64 using the guide for casting in the link below but get invalid entries error. https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules

Is there a way to take a difference for DateTime values? The values are like 12-03-04 00:00:000 EST?

Upvotes: 0

Views: 265

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

Since you want the difference between 2 DATETIME, then the correct answer is DATETIME_DIFF:

SELECT DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
  DATETIME "2008-12-25 15:30:00", DAY)

# 559

Upvotes: 3

Related Questions