BigQuery comparing DATE and TIMESTAMP

Question

Here is my example I am using in MySQL. However, in BigQuery, my OnSite timestamp is a DATE and my Documents timestamp is a TIMESTAMP.

BigQuery is having trouble with the below query because I get the message:

No matching signature for function DATE for argument types: DATE. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64) at [8:146]

Does anyone know what I need to do to make it so the query works with comparing DATEs and TIMESTAMPs?

Schema (MySQL v5.7)

CREATE TABLE OnSite
    (`uid` varchar(55), `worksite_id`  varchar(55), `timestamp` datetime)
;

INSERT INTO OnSite
    (`uid`, `worksite_id`, `timestamp`)
VALUES
  ("u12345", "worksite_1", '2019-01-01'),
  ("u12345", "worksite_1", '2019-01-02'),
  ("u12345", "worksite_1", '2019-01-03'),
  ("u12345", "worksite_1", '2019-01-04'),
  ("u12345", "worksite_1", '2019-01-05'),
  ("u12345", "worksite_1", '2019-01-06'),
  ("u1", "worksite_1", '2019-01-01'),
  ("u1", "worksite_1", '2019-01-02'),
  ("u1", "worksite_1", '2019-01-05'),
  ("u1", "worksite_1", '2019-01-06')

;


CREATE TABLE Documents
    (`document_id` varchar(55), `uid` varchar(55), `worksite_id`  varchar(55), `type` varchar(55), `timestamp` datetime)
;

INSERT INTO Documents
    (`document_id`, `uid`, `worksite_id`, `type`, `timestamp`)

VALUES
  ("1",     "u12345",   "worksite_1", 'work_permit',    '2019-01-01 00:00:00'),
  ("2",     "u12345",   "worksite_2", 'job',            '2019-01-02 00:00:00'),
  ("3",     "u12345",   "worksite_1", 'work_permit',    '2019-01-03 00:00:00'),
  ("4",     "u12345",   "worksite_2", 'job',            '2019-01-04 00:00:00'),
  ("5",     "u12345",   "worksite_1", 'work_permit',    '2019-01-05 00:00:00'),
  ("6",     "u12345",   "worksite_2", 'job',            '2019-01-06 00:00:00'),
  ("7",     "u12345",   "worksite_1", 'work_permit',    '2019-01-07 00:00:00'),
  ("8",     "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00'),
  ("9",     "u12345",   "worksite_1", 'job',            '2019-01-09 00:00:00'),
  ("10",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00'),
  ("11",    "u12345",   "worksite_1", 'work_permit',    '2019-01-09 00:00:00'),
  ("12",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00'),
  ("13",    "u12345",   "worksite_1", 'job',            '2019-01-09 00:00:00'),
  ("14",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00'),
  ("15",    "u12345",   "worksite_1", 'work_permit',    '2019-01-09 00:00:00')

;

Query #1

SELECT
  IFNULL(OnSite.worksite_id, Documents.worksite_id) as `Worksite`,
  DATE(IFNULL(OnSite.timestamp, Documents.timestamp)) as `Date`,
  COUNT(Documents.worksite_id) as `Users_on_Site`,
  COUNT(DISTINCT OnSite.uid) as `Completed`

FROM OnSite
  LEFT JOIN Documents ON OnSite.worksite_id = Documents.worksite_id AND DATE(OnSite.timestamp) = DATE(Documents.timestamp)
GROUP BY `Date`, `Worksite`;

| Worksite   | Date       | Users_on_Site | Completed |
| ---------- | ---------- | ------------- | --------- |
| worksite_1 | 2019-01-01 | 2             | 2         |
| worksite_1 | 2019-01-02 | 0             | 2         |
| worksite_1 | 2019-01-03 | 1             | 1         |
| worksite_1 | 2019-01-04 | 0             | 1         |
| worksite_1 | 2019-01-05 | 2             | 2         |
| worksite_1 | 2019-01-06 | 0             | 2         |

View on DB Fiddle

What if you use SQL type casting like `LEFT JOIN Documents ON OnSite.worksite_id = Documents.worksite_id AND DATE(TIMESTAMP OnSite.timestamp) = DATE(TIMESTAMP Documents.timestamp)` or `LEFT JOIN Documents ON OnSite.worksite_id = Documents.worksite_id AND DATE(OnSite.timestamp::timestamp) = DATE(Documents.timestamp::timestamp)` ? — Raymond Nijland, Jan 17 '19 at 15:27
you need to be clear about what you use - `mysql` or `bigquery` — Mikhail Berlyant, Jan 17 '19 at 15:29
@MikhailBerlyant sorry I thought I was clear in the Title. I tagged both because I felt like they were similar skillsets but I will make sure I remove the mysql tag. — bryan, Jan 17 '19 at 15:31
i really recommend you to use proper tags as this misleads and ends up with "junk" answers — Mikhail Berlyant, Jan 17 '19 at 15:32
please clarify - are you using BigQuery Legacy SQL or Standard SQL. this would make quite a difference — Mikhail Berlyant, Jan 17 '19 at 19:24
@MikhailBerlyant I didn't even know there was a Legacy option but I have checked and I am on Standard. — bryan, Jan 17 '19 at 19:37

Mikhail Berlyant · Accepted Answer · 2019-01-17T20:53:28.973

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  IFNULL(OnSite.worksite_id, Documents.worksite_id) AS `Worksite`,
  IFNULL(OnSite.timestamp, DATE(Documents.timestamp)) AS `DATE`,
  COUNT(Documents.worksite_id) AS `Users_on_Site`,
  COUNT(DISTINCT OnSite.uid) AS `Completed`
FROM `project.dataset.OnSite` OnSite
LEFT JOIN `project.dataset.Documents` Documents 
ON OnSite.worksite_id = Documents.worksite_id 
AND OnSite.timestamp = DATE(Documents.timestamp)
GROUP BY `DATE`, `Worksite`

if to apply to sample data from your question

WITH `project.dataset.OnSite` AS (
  SELECT "u12345" uid, "worksite_1" worksite_id, DATE '2019-01-01' `TIMESTAMP` UNION ALL
  SELECT "u12345", "worksite_1", '2019-01-02' UNION ALL
  SELECT "u12345", "worksite_1", '2019-01-03' UNION ALL
  SELECT "u12345", "worksite_1", '2019-01-04' UNION ALL
  SELECT "u12345", "worksite_1", '2019-01-05' UNION ALL
  SELECT "u12345", "worksite_1", '2019-01-06' UNION ALL
  SELECT "u1", "worksite_1", '2019-01-01' UNION ALL
  SELECT "u1", "worksite_1", '2019-01-02' UNION ALL
  SELECT "u1", "worksite_1", '2019-01-05' UNION ALL
  SELECT "u1", "worksite_1", '2019-01-06' 
), `project.dataset.Documents` AS (
  SELECT "1" document_id,     "u12345" uid,   "worksite_1" worksite_id, 'work_permit' type,    TIMESTAMP '2019-01-01 00:00:00' `TIMESTAMP` UNION ALL
  SELECT "2",     "u12345",   "worksite_2", 'job',            '2019-01-02 00:00:00' UNION ALL
  SELECT "3",     "u12345",   "worksite_1", 'work_permit',    '2019-01-03 00:00:00' UNION ALL
  SELECT "4",     "u12345",   "worksite_2", 'job',            '2019-01-04 00:00:00' UNION ALL
  SELECT "5",     "u12345",   "worksite_1", 'work_permit',    '2019-01-05 00:00:00' UNION ALL
  SELECT "6",     "u12345",   "worksite_2", 'job',            '2019-01-06 00:00:00' UNION ALL
  SELECT "7",     "u12345",   "worksite_1", 'work_permit',    '2019-01-07 00:00:00' UNION ALL
  SELECT "8",     "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00' UNION ALL
  SELECT "9",     "u12345",   "worksite_1", 'job',            '2019-01-09 00:00:00' UNION ALL
  SELECT "10",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00' UNION ALL
  SELECT "11",    "u12345",   "worksite_1", 'work_permit',    '2019-01-09 00:00:00' UNION ALL
  SELECT "12",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00' UNION ALL
  SELECT "13",    "u12345",   "worksite_1", 'job',            '2019-01-09 00:00:00' UNION ALL
  SELECT "14",    "u12345",   "worksite_2", 'work_permit',    '2019-01-09 00:00:00' UNION ALL
  SELECT "15",    "u12345",   "worksite_1", 'work_permit',    '2019-01-09 00:00:00' 
)

result will be as expected

Row Worksite    Date        Users_on_Site   Completed    
1   worksite_1  2019-01-01  2               2    
2   worksite_1  2019-01-02  0               2    
3   worksite_1  2019-01-03  1               1    
4   worksite_1  2019-01-04  0               1    
5   worksite_1  2019-01-05  2               2    
6   worksite_1  2019-01-06  0               2

I'm getting a weird error on Line 9: `ON OnSite.worksite_id = Documentimestamp.worksite_id ` (Unrecognized name) — bryan, Jan 17 '19 at 20:47
Hmm for some reason it's the same issue on the same line when I put it into BigQuery. — bryan, Jan 17 '19 at 20:58
you should check your query again. i just run/tested it again with success and result as it is in answer. if still issue - show the error message — Mikhail Berlyant, Jan 17 '19 at 21:00
You're right, it was a type on my end. Thank you very much. This makes a lot of sense and I'm learning more and more every time you help me. I really appreciate it. Adding `ORDER BY "DATE" desc` to the end wouldn't mess things up right? — bryan, Jan 17 '19 at 21:01
Mikhail, if I wanted to filter this by date? The best way I can think of doing it is adding this query: `WHERE Documents.ts BETWEEN "2019-01-01" AND "2019-10-10" AND OnSite.ts BETWEEN "2019-01-01" AND "2019-10-10"` I feel like this is a hacky solution. If you had any guidance I'd love to know — bryan, Jan 17 '19 at 22:43
I think you can just do - `WHERE OnSite.timestamp BETWEEN "2019-01-01" AND "2019-10-10"` - don't see any "hackiness" here - usual way of filtering — Mikhail Berlyant, Jan 17 '19 at 22:46

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

In BigQuery documentation, it is explained that DATE function accepts following input :

DATE(year, month, day) : Constructs a DATE from INT64 values representing the year, month, and day.

DATE(timestamp_expression[, timezone]) : Converts a timestamp_expression to a DATE data type. It supports an optional parameter to specify a timezone. If no timezone is specified, the default timezone, UTC, is used.

In your use case, it seems like the value you are passing to DATE is already a datetime. For this purpose, you could use DATETIME_TRUNC, like :

DATETIME_TRUNC(IFNULL(OnSite.timestamp, Documents.timestamp), DAY)

score 0 · Answer 3 · answered Jan 17 '19 at 20:16

Why don't you just force-cast everything and make life easier :-)? All these should work:

select 
   date(timestamp('2019-01-02')), 
   date(timestamp('2019-01-02 00:00:00')), 
   date(timestamp(null)))

So, in your if null statement:

SELECT
  IFNULL(OnSite.worksite_id, Documents.worksite_id) as `Worksite`,
  IFNULL(date(datetime(OnSite.timestamp)),date(datetime(Documents.timestamp))) as `Date`,
  COUNT(Documents.worksite_id) as `Users_on_Site`,
  COUNT(DISTINCT OnSite.uid) as `Completed`
FROM OnSite
  LEFT JOIN Documents ON OnSite.worksite_id = Documents.worksite_id AND DATE(datetime(OnSite.timestamp)) = DATE(datetime(Documents.timestamp))
GROUP BY `Date`, `Worksite`;

BigQuery comparing DATE and TIMESTAMP

3 Answers3