CODEWITHSUNDEEP
google-bigquery
user3447653
user3447653

Reputation: 4148

Compare dates using relational operators in google big query

I am trying to select the records whose DateTime in table tb2 is greater than or equal to MinDateTime of tb1and whose DateTime of tb2 is less MaxDateTime of table tb1.

I am getting the error “Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name.”

Not sure whether i am not allowed to compare dates like this in the query. 

SELECT
 *
FROM (
    SELECT
        c.Id2 AS Id,
        d.DateTime AS DateTime,
        c.Latitude AS Latitude,
        c.Longitude AS Longitude
    FROM (
            SELECT
                Id1,MAX(DateTime) AS MaxDatetime,
                MIN(DateTime) AS MinDateTime
            FROM [tb1]
            GROUP BY VehicleId) AS d
    INNER JOIN (
         SELECT
                Latitude, Longitude, Id2, DateTime
                FROM [tb2]) AS c
ON
    d.Id1 = c.Id2
    AND c.DateTime >= d.MinDateTime
    AND c.DateTime < d.MaxDateTime)

Upvotes: 1

Views: 466

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

To expand on Mikhail's note:

BigQuery legacy SQL requires that join conditions are simple equalities. Using standard SQL (uncheck "Use Legacy SQL" under "Show Options") you should be able to execute the query with just a small adjustment of escaping the table names with backticks ` instead, something like:

SELECT
 *
FROM (
    SELECT
        c.Id2 AS Id,
        d.DateTime AS DateTime,
        c.Latitude AS Latitude,
        c.Longitude AS Longitude
    FROM (
            SELECT
                Id1,MAX(DateTime) AS MaxDatetime,
                MIN(DateTime) AS MinDateTime
            FROM `tb1`
            GROUP BY VehicleId) AS d
    INNER JOIN (
         SELECT
                Latitude, Longitude, Id2, DateTime
                FROM `tb2`) AS c
ON
    d.Id1 = c.Id2
    AND c.DateTime >= d.MinDateTime
    AND c.DateTime < d.MaxDateTime);

If you do want to execute the query using legacy SQL, you will need to express the non-equality conditions for the join as a separate filter, e.g.:

SELECT
 *
FROM (
    SELECT
        c.Id2 AS Id,
        d.DateTime AS DateTime,
        c.Latitude AS Latitude,
        c.Longitude AS Longitude
    FROM (
            SELECT
                Id1,MAX(DateTime) AS MaxDatetime,
                MIN(DateTime) AS MinDateTime
            FROM [tb1]
            GROUP BY VehicleId) AS d
    INNER JOIN (
         SELECT
                Latitude, Longitude, Id2, DateTime
                FROM [tb2]) AS c
ON
    d.Id1 = c.Id2
WHERE
    c.DateTime >= d.MinDateTime
    AND c.DateTime < d.MaxDateTime)

Upvotes: 1

Related Questions