Convenience methods for some commonly used timestamps in analytics or reporting.

postgres_timestamp_functions.sql

    CREATE OR REPLACE FUNCTION day_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION day_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt) + interval '1 day' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION week_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION week_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt) + interval '1 week' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION month_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION month_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt) + interval '1 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION quarter_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION quarter_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt) + interval '3 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION year_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION year_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt) + interval '1 year' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION yesterday_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt - interval '1 day');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION yesterday_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt - interval '1 day') 
               + interval '1 day' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_week_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt - interval '1 week');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_week_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt - interval '1 week') + interval '1 week' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_month_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt - interval '1 month');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_month_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt - interval '1 month') + interval '1 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_quarter_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt - interval '3 month');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_quarter_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt - interval '3 month') + interval '3 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_year_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt - interval '1 year');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_year_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt - interval '1 year') + interval '1 year' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION tomorrow_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt + interval '1 day');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION today_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION today_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt) 
               + interval '1 day' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_week_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_week_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt) + interval '1 week' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_month_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_month_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt) + interval '1 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_quarter_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_quarter_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt) + interval '3 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_year_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION this_year_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt) + interval '1 year' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION tomorrow_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt + interval '1 day') 
               + interval '1 day' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_week_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt + interval '1 week');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_week_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('week', dt + interval '1 week') + interval '1 week' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_month_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt + interval '1 month');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_month_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('month', dt + interval '1 month') + interval '1 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_quarter_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt + interval '3 month');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_quarter_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('quarter', dt + interval '3 month') + interval '3 month' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_year_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt + interval '1 year');
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION next_year_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)    
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('year', dt + interval '1 year') + interval '1 year' - interval '1 microsecond';
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_n_hours_begin(hours integer, dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('hour', dt - (hours * interval '1 hour'));
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_24_hours_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN last_n_hours_begin(24, dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_n_days_begin(days integer, dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN date_trunc('day', dt - (days * interval '1 day'));
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_7_days_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN last_n_days_begin(7, dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_30_days_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN last_n_days_begin(30, dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_90_days_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN last_n_days_begin(90, dt);
    END;
    $$ LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION last_365_days_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS timestamp with time zone AS $$
    BEGIN
        RETURN last_n_days_begin(365, dt);
    END;
    $$ LANGUAGE plpgsql;