CODEWITHSUNDEEP
google-bigquery
Filipe Ferminiano
Filipe Ferminiano

Reputation: 8791

Check if timestamp is contained in date

I'm trying to check if a datetime is contained in current date, but I'm not veing able to do it.

This is my query:

select 
date(timestamp) as event_date,
count(*)
from pixel_logs.full_logs f
where 1=1
where event_date = CUR_DATE()

How can I fix it?

Upvotes: 0

Views: 284

Answers (1)

Chris Hill
Chris Hill

Reputation: 121

Like Mikhail said, you need to use CURRENT_DATE(). Also, count(*) requires you to GROUP BY the date in your example. I do not know how your data is formatted, but one way to modify your query:

#standardSQL
WITH
  table AS (
SELECT
  1494977678 AS timestamp_secs) -- Current timestamp (in seconds)

SELECT
  event_date,
  COUNT(*) as count
FROM (
  SELECT
    DATE(TIMESTAMP_SECONDS(timestamp_secs)) AS event_date,
    CURRENT_DATE()
  FROM
    table)
WHERE
  event_date = CURRENT_DATE()
GROUP BY
  event_date;

Upvotes: 4

Related Questions