CODEWITHSUNDEEP
sqlgoogle-bigquery
Muhammad Aqeel
Muhammad Aqeel

Reputation: 275

How to Calculate avg no of records added per day in BigQuery.?

I have a table in BigQuery having a column Published_date with a datatype of "Timestamp". I want to calculate avg no of rows added per day (for a specific month) in that table. I have the following query 

SELECT AVG(Num_Rows) 
FROM (SELECT [Day]=DAY( Published_Date ), Num_Rows=COUNT(*)
FROM `mytable`
WHERE Published_Date BETWEEN '20190729' AND '20190729 '
GROUP BY DAY( Published_Date ) ) AS Z

But its generating the following error

Could not cast literal "20190729" to type TIMESTAMP

How should I deal with timestamp because I only need the date from timestamp column?

Upvotes: 0

Views: 2196

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

I want to calculate avg no of rows added per day (for a specific month) in that table

Below example for BigQuery Standard SQL 

#standardSQL
SELECT AVG(Num_Rows) AS avg_rows_per_day
FROM (
  SELECT DATE(Published_Date) AS day, COUNT(*) AS Num_Rows
  FROM `project.dataset.mytable`
  WHERE DATE(Published_Date) BETWEEN '2019-07-01' AND '2019-07-31'
  GROUP BY day
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use explicit conversion:

WHERE Published_Date BETWEEN TIMESTAMP('2019-07-29') AND TIMESTAMP('2019-07-29')

Note that you have a column called "_date", but the error is saying that the value is a timestamp. I find this confusing. We use a convention of using _ts in columns that are timestamps (and _dt for datetime and _date for date).

Why is this important? The timestamp is UTC. So you might need to be careful about timezones and time components -- which is not obvious in a column called Publish_Date.

Upvotes: 0

Related Questions