Skip to content

Instantly share code, notes, and snippets.

@thiamteck

thiamteck/sliding-windows-label-by-hour.sql

Created May 23, 2024 04:28
Show Gist options
  • Save thiamteck/1a80547149177369701b16e76b618b64 to your computer and use it in GitHub Desktop.
Save thiamteck/1a80547149177369701b16e76b618b64 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sliding-windows-label-by-hour.sql
SELECT
tm AS transaction_timestamp,
-- Calculate the window label
DATE_FORMAT(
DATE_ADD(tm, INTERVAL ((60 - MINUTE(NOW()))*60)-1 SECOND),
CONCAT('%Y-%m-%d %H:', MINUTE(NOW()))
) AS window_label
FROM
subscriber_history_tracking
WHERE
result = 1
AND tm >= "2024-03-04 00:24:00"
LIMIT 100;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment