update_checkins.sql

-- HELPER FUNCTIONS

-- returns the day of the month from a date
CREATE OR REPLACE FUNCTION day_of_month(dt date) RETURNS INTEGER AS
$$
    SELECT DATE_PART('days', dt)::INTEGER
$$ LANGUAGE SQL;

-- returns the numeric (1-12) value for a month from a date
CREATE OR REPLACE FUNCTION month_index(dt date) RETURNS INTEGER AS
$$
    SELECT DATE_PART('month', dt)::INTEGER
$$ LANGUAGE SQL;

-- updates the month checkin record for a given date
CREATE OR REPLACE FUNCTION set_checkin(x integer, checkin_dt date) RETURNS INTEGER AS
$$
    SELECT (x | (1 << 31 - day_of_month(checkin_dt)))
$$ LANGUAGE SQL;


-- check-in for date
UPDATE streaks_checkins
SET checkins[month_index(%(date)s)] = set_checkin(checkins[month_index(%(date)s)], %(date)s)
WHERE user_id = %(user_id)s and year = %(year)s;