Menu
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
859
If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
Datetime type: comprises both calendar date and time. It does not store time zone information: YYYY-MM-DD HH:MM:SS (e.g. ). Timestamp type: comprises date, time, and time zone information.
The format is: YYYY-MM-DD HH:MM:SS (e.g. 2021-05-15 16:45:23). Timestamp type: Date, time, and time zone information are all included in timestamps. If no time zone is given, the format falls back to UTC.
Subtracting a unit Subtracting a specific amount of days, weeks, months, quarters, or years from a date can be done using the DATE_SUB function. The first argument takes a date and the second argument takes an interval, a numeric value, and a unit. The supported units (DATE_PART) are: Days: DAY.
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
84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
