View query result in table bigquery

Question

I am categorizing the time into morning and night and count. But quite confuse how to use it just to view the result in a form of table.

Code:

 SELECT
      COUNTIF(time BETWEEN '04:00:00' AND '12:00:00') AS morning_events,
      COUNTIF(time < '04:00:00' OR time > '20:00:00') AS night_events
    FROM (
      SELECT
        TIME_TRUNC(TIME(Request_Timestamp), SECOND) AS Time
      FROM table
    );

So far I managed to get the count function (refer 1st pic) but not sure how can view second table that showing the list of each session (morning/night) for EACH time (refer 2nd pic).

rmesteves rmesteves · Accepted Answer · 2020-06-10T09:36:14

I dont know which criteria you are using to define afternoon and evening but considering only day and night you could do something like this:

SELECT time, 
case
  when  time BETWEEN '04:00:00' AND '12:00:00' 
  then  "morning"
  when  time < '04:00:00' OR time > '20:00:00' 
  then  "night"
  # You can add other conditions here
  else "nothing"
end
FROM (
      SELECT
        TIME_TRUNC(TIME(Request_Timestamp), SECOND) AS Time
      FROM table
    );

View query result in table bigquery

1 Answers