Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE FUNCTION first_of_month(val date) RETURNS date
    IMMUTABLE
    STRICT
    PARALLEL SAFE
    LANGUAGE sql
RETURN (DATE_TRUNC('MONTH'::text, (val)::timestamp WITH TIME ZONE))::date;

CREATE FUNCTION last_of_month(val date) RETURNS date
    IMMUTABLE
    STRICT
    PARALLEL SAFE
    LANGUAGE sql
RETURN ((DATE_TRUNC('MONTH'::text, (val)::timestamp WITH TIME ZONE) + '1 mon -1 days'::interval))::date;

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION rounded_end(end_ts timestamp WITH TIME ZONE, now_ts timestamp WITH TIME ZONE) RETURNS date
    IMMUTABLE
    STRICT
    PARALLEL SAFE
    LANGUAGE sql
RETURN CASE
           WHEN ((first_of_month((now_ts)::date) = first_of_month((end_ts)::date)) AND (end_ts >= now_ts))
               THEN last_of_month((end_ts)::date)
           ELSE (first_of_month((end_ts)::date) - 1) END;

CREATE FUNCTION

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
WITH timestamps AS (SELECT GENERATE_SERIES(timestamp '2014-01-10 20:00:00' +
                                           RANDOM() * (timestamp '2014-01-20 20:00:00' -
                                                       timestamp '2014-01-10 10:00:00'),
                                           timestamp '2025-01-10 20:00:00' +
                                           RANDOM() * (timestamp '2025-01-20 20:00:00' -
                                                       timestamp '2025-01-10 10:00:00'),
                                           '1 hour') AS ts)
SELECT CASE
           WHEN ((first_of_month((NOW())::date) = first_of_month((ts)::date)) AND (ts >= NOW()))
               THEN last_of_month((ts)::date)
           ELSE (first_of_month((ts)::date) - 1) END
FROM timestamps;

QUERY PLAN

CTE Scan on timestamps  (cost=5.03..1047.53 rows=1000 width=4) (actual time=0.246..3296.799 rows=96434 loops=1)

  Output: CASE WHEN ((first_of_month((now())::date) = first_of_month((timestamps.ts)::date)) AND (timestamps.ts >= now())) THEN last_of_month((timestamps.ts)::date) ELSE (first_of_month((timestamps.ts)::date) - 1) END

  Buffers: temp written=211

  CTE timestamps

    ->  ProjectSet  (cost=0.00..5.03 rows=1000 width=8) (actual time=0.045..73.683 rows=96434 loops=1)

          Output: generate_series(('2014-01-10 20:00:00'::timestamp without time zone + (random() * '10 days 10:00:00'::interval)), ('2025-01-10 20:00:00'::timestamp without time zone + (random() * '10 days 10:00:00'::interval)), '01:00:00'::interval)

          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

Planning Time: 0.183 ms

Execution Time: 3309.669 ms

EXPLAIN

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
WITH timestamps AS (SELECT GENERATE_SERIES(timestamp '2014-01-10 20:00:00' +
                                           RANDOM() * (timestamp '2014-01-20 20:00:00' -
                                                       timestamp '2014-01-10 10:00:00'),
                                           timestamp '2025-01-10 20:00:00' +
                                           RANDOM() * (timestamp '2025-01-20 20:00:00' -
                                                       timestamp '2025-01-10 10:00:00'),
                                           '1 hour') AS ts)
SELECT rounded_end(ts, NOW())
FROM timestamps;

QUERY PLAN

CTE Scan on timestamps  (cost=5.03..280.03 rows=1000 width=4) (actual time=0.247..21926.064 rows=96525 loops=1)

  Output: rounded_end((timestamps.ts)::timestamp with time zone, now())

  Buffers: temp written=212

  CTE timestamps

    ->  ProjectSet  (cost=0.00..5.03 rows=1000 width=8) (actual time=0.005..129.023 rows=96525 loops=1)

          Output: generate_series(('2014-01-10 20:00:00'::timestamp without time zone + (random() * '10 days 10:00:00'::interval)), ('2025-01-10 20:00:00'::timestamp without time zone + (random() * '10 days 10:00:00'::interval)), '01:00:00'::interval)

          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

Planning Time: 0.107 ms

Execution Time: 21998.703 ms

EXPLAIN