CODEWITHSUNDEEP
sqlgoogle-bigquery
daniel
daniel

Reputation: 35703

Bigquery count with 'empty' dates

I do a query to get row counts by date:

SELECT TIMESTAMP_TRUNC(TIMESTAMP_MICROS( CAST(CAST(datetime AS NUMERIC)*1000 AS INT64)),DAY ) AS timestamp1, count(datetime)
FROM `eventlogs` 
WHERE ( CAST(datetime AS NUMERIC) > 1544375081371.431 ) AND message LIKE '%mymessage%'
GROUP BY timestamp1 
ORDER BY timestamp1
LIMIT 10000

this gives me the results like:

1   2018-12-10 00:00:00 UTC 561
2   2018-12-11 00:00:00 UTC 1473
3   2018-12-12 00:00:00 UTC 650
4   2018-12-13 00:00:00 UTC 407
5   2018-12-14 00:00:00 UTC 283
6   2018-12-15 00:00:00 UTC 1
7   2018-12-17 00:00:00 UTC 213
8   2018-12-18 00:00:00 UTC 583

Is there a way to get the missing date 2018-12-16 with a 0 ?

Upvotes: 1

Views: 636

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could generate calendar table(pseudocode)

SELECT cal_day, count(e.datetime) AS cnt
FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2018-12-10'), CURRENT_DATE(), INTERVAL 1 DAY)
) AS cal_day
LEFT JOIN `eventlogs` e
  ON cal.d = CAST(e.datetime AS DATE)
WHERE ( CAST(datetime AS NUMERIC) > 1544375081371.431 )
  AND message LIKE '%mymessage%'
GROUP BY cal_day
ORDER BY cal_day
LIMIT 10000

Upvotes: 2

Related Questions