CODEWITHSUNDEEP
sqlgoogle-bigquery
Girish Chhatani
Girish Chhatani

Reputation: 53

How to get difference in hours between two datetime in floating or decimal in bigquery

I'm facing issue while calculating difference between two dates in decimal.

Date1:- 2019-07-02 16:42:06
Date2:- 2019-07-02 15:53:01

Expected Result:- 0:49:05

select datetime_diff('2019-07-02 16:42:06','2019-07-02 15:53:01',Hour)

BigQuery Output:-1

Upvotes: 5

Views: 1115

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Expected Result:- 0:49:05

Below is for BigQuery Standard SQL

  SELECT FORMAT('%i%s', 
    DIV(DATETIME_DIFF(date1, date2, MINUTE), 60),
    FORMAT_TIME(':%M:%S', TIME(DATETIME_ADD(DATETIME(TIMESTAMP(DATE(1970, 1, 1))), INTERVAL DATETIME_DIFF(date1, date2, SECOND) SECOND)))
  ) AS diff

For DATETIME '2019-07-05 17:42:06' date1, DATETIME '2019-07-02 15:53:01' date2 it gives

diff     
73:49:05

For DATETIME '2019-07-02 17:42:06' date1, DATETIME '2019-07-02 15:53:01' date2 result is

diff     
1:49:05

For DATETIME '2019-07-02 16:42:06' date1, DATETIME '2019-07-02 15:53:01' date2 (as in your question) result is

diff     
0:49:05

If you know that the difference will be within 24 hours you can use simpler statement as below

FORMAT_TIME('%T', TIME(DATETIME_ADD(DATETIME(TIMESTAMP(DATE(1970, 1, 1))), INTERVAL DATETIME_DIFF(date1, date2, SECOND) SECOND)))

For last two examples result will be respectively 01:49:05 and 00:49:05

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you want a decimal value, then use a smaller unit and arithmetic:

select datetime_diff('2019-07-02 16:42:06', '2019-07-02 15:53:01', second) / (60 * 60.0) as decimal_hours

Your sample result is not a decimal value.

Upvotes: 3

Related Questions