Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE action_events (
  id serial PRIMARY KEY
, timestamp timestamp NOT NULL
, event_name varchar(255) NOT NULL
);

INSERT INTO action_events (timestamp, event_name)
VALUES
  ('2023-03-01 09:00:00', 'exercise'),
  ('2023-03-02 09:00:00', 'exercise'),
  ('2023-03-03 09:00:00', 'exercise'),
  ('2023-03-03 13:00:00', 'exercise'),
  ('2023-03-04 09:00:00', 'exercise'),
  ('2023-03-05 09:00:00', 'exercise'),
  ('2023-03-06 09:00:00', 'exercise'),
  ('2023-03-07 09:00:00', 'exercise'),
  ('2023-03-07 16:32:00', 'soda'),
  ('2023-03-08 09:00:00', 'exercise'),
  ('2023-03-09 09:00:00', 'exercise'),
  ('2023-03-09 13:00:00', 'exercise'),
  ('2023-03-10 09:00:00', 'exercise'),
  ('2023-03-11 09:00:00', 'exercise'),
  ('2023-03-12 09:00:00', 'exercise'),
  ('2023-03-13 09:00:00', 'exercise'),
  ('2023-03-14 09:00:00', 'exercise'),
  ('2023-03-16 09:00:00', 'exercise'),
  ('2023-03-17 09:00:00', 'exercise'),
  ('2023-03-17 16:32:00', 'soda'),
  ('2023-03-18 09:00:00', 'exercise'),
  ('2023-03-19 09:00:00', 'exercise'),
  ('2023-03-19 13:00:00', 'exercise'),
  ('2023-03-19 16:32:00', 'soda'),
  ('2023-03-19 22:00:00', 'exercise'), -- Notice this is on the 19th UTC, but the 20th MST. Should still count as streak
  ('2023-03-21 09:00:00', 'exercise'),
  ('2023-03-22 09:00:00', 'exercise'),
  ('2023-03-22 13:00:00', 'exercise'),
  ('2023-03-23 09:00:00', 'exercise'),
  ('2023-03-23 13:00:00', 'exercise'),
  ('2023-03-24 09:00:00', 'exercise'),
  ('2023-03-24 16:32:00', 'soda'),
  ('2023-03-25 09:00:00', 'exercise'),
  ('2023-03-25 16:32:00', 'soda'),
  ('2023-03-26 09:00:00', 'exercise'),
  ('2023-03-27 09:00:00', 'exercise'),
  ('2023-03-28 09:00:00', 'exercise'),
  ('2023-03-29 09:00:00', 'exercise'),
  ('2023-03-29 13:00:00', 'exercise'),
  ('2023-03-30 09:00:00', 'exercise');

CREATE TABLE

INSERT 0 40

streak_count	event_name	timezone	start_date	end_date
13	exercise	MST	2023-03-21 09:00:00	2023-03-30 09:00:00

SELECT 1