time related function for epoch timestamp in bigquery

Question

when querying a table which has a column "timestamp" (epoch timestamp, UTC, milliseconds, type integer in the bigquery table)

i want to be able to say: timestamp between one_week_ago and now

without specifying the exact timestamps in each query. i should add that i know the following working query:

WITH timerange AS
(SELECT *,
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR) AS one_week_ago,
CURRENT_TIMESTAMP() AS now,
TIMESTAMP_SECONDS(timestamp) AS measurement_time
FROM table_name),

grouped AS
(SELECT field1, field2, count(*) count
FROM timerange
WHERE measurement_time BETWEEN one_week_ago AND now 
GROUP BY field1, field2
)

SELECT * FROM timerange
WHERE field2 = "example"

but why am i not simply able to say: timestamp between function_call1 and function_call2 ?

these are examples of the timestamps: 1491544587, 1491422047, 1491882866, 1491881903 1491436515, 1491436771, 1491436593, 1491436621, 1491436390, 1491436334

https://cloud.google.com/bigquery/docs/reference/legacy-sql https://cloud.google.com/bigquery/docs/reference/standard-sql/

Mosha Pasumansky Mosha Pasumansky · Accepted Answer · 2017-05-03T00:31:01

You can certainly say in Standard SQL like you want:

SELECT *
FROM table
WHERE TIMESTAMP_SECONDS(timestamp) BETWEEN
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR)
  AND CURRENT_TIMESTAMP()

time related function for epoch timestamp in bigquery

1 Answers