-- Author: Marquin Smith
-- Description:
-- Calculate the how long ago (in seconds) since the 'daily_stock_price' table
-- was modified
/*
SELECT
last_modified_time,
-- last_modified_time in timestamp form
TIMESTAMP_MILLIS(last_modified_time) AS last_modified_timestamp,
CURRENT_TIMESTAMP() AS my_current_time,
-- calculate the difference from modified time and current time (using unix milliseconds)
(UNIX_MILLIS(CURRENT_TIMESTAMP() ) - last_modified_time) / 1000 AS seconds_diff,
CASE
WHEN (UNIX_MILLIS(CURRENT_TIMESTAMP() ) - last_modified_time) / 1000 > 600
THEN 'last modified more than 5 mins ago'
ELSE ''
END AS modified_ago_flag
FROM `{PROJECT_ID}.{DATASET_ID}`.__TABLES__
WHERE
table_id = '{TABLE_ID}'
*/
SELECT
last_modified_time,
-- last_modified_time in timestamp form
TIMESTAMP_MILLIS(last_modified_time) AS last_modified_timestamp,
CURRENT_TIMESTAMP() AS my_current_time,
-- calculate the difference from modified time and current time (using unix milliseconds)
(UNIX_MILLIS(CURRENT_TIMESTAMP() ) - last_modified_time) / 1000 AS seconds_diff,
CASE
WHEN (UNIX_MILLIS(CURRENT_TIMESTAMP() ) - last_modified_time) / 1000 > 600
THEN 'last modified more than 5 mins ago'
ELSE ''
END AS modified_ago_flag
FROM `marquin-personal-tools.marquinsmith_dot_com`.__TABLES__
WHERE
table_id = 'daily_stock_price'
