CODEWITHSUNDEEP
google-bigquery
Deviling Master
Deviling Master

Reputation: 3113

Add TIME to DATETIME value

I can see from documentation that DATETIME_ADD only works with INT64 values, not TIME objects.

I have a DATETIME which represents the point of start, and then a duration in a TIME object 

WITH input AS (
  SELECT
    DATE(2018,03,05) AS start_date,
    TIME(5,0,0) AS start_time,
    TIME(8,0,0) AS duration
)

SELECT
  *,
  DATETIME(start_date,start_time) AS start_datetime,
  DATETIME_ADD(
      DATETIME_ADD(
        DATETIME_ADD(
          DATETIME(start_date,start_time), 
          INTERVAL EXTRACT(HOUR FROM duration) HOUR
        ),
        INTERVAL EXTRACT(MINUTE FROM duration) MINUTE
      ),
        INTERVAL EXTRACT(SECOND FROM duration) SECOND
    ) AS end_datetime

FROM input

Exists a nicer way to add the 3 values of the TIME object (hours ,minutes ,seconds) to the given DATETIME object?

Upvotes: 1

Views: 7434

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Below is for BigQuery Standard SQL 

WITH input AS (
  SELECT
    DATE(2018,03,05) AS start_date,
    TIME(5,0,0) AS start_time,
    TIME(8,0,0) AS duration
)
SELECT
  *,
  DATETIME(start_date,start_time) AS start_datetime,
  DATETIME_ADD(
      DATETIME_ADD(
        DATETIME_ADD(
          DATETIME(start_date,start_time), 
          INTERVAL EXTRACT(HOUR FROM duration) HOUR
        ),
        INTERVAL EXTRACT(MINUTE FROM duration) MINUTE
      ),
        INTERVAL EXTRACT(SECOND FROM duration) SECOND
    ) AS end_datetime,
    DATETIME_ADD(
      DATETIME(start_date,start_time), 
      INTERVAL DATETIME_DIFF(DATETIME(start_date,duration), DATETIME(start_date), SECOND) SECOND
    ) end_datetime_nicer_way
FROM input

Upvotes: 5

Related Questions