determining number of weeks between date range in postgres

gistfile1.sql

-- First attempt:
EXPLAIN ANALYZE SELECT count(DISTINCT date_trunc('week',s)) FROM generate_series('1900-01-01'::date,CURRENT_DATE,interval '1 day') AS s;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12.51..12.53 rows=1 width=8) (actual time=72.552..72.553 rows=1 loops=1)
   ->  Function Scan on generate_series s  (cost=0.01..10.01 rows=1000 width=8) (actual time=24.495..27.619 rows=40975 loops=1)
 Total runtime: 73.005 ms
(3 rows)

SELECT count(DISTINCT date_trunc('week',s)) FROM generate_series('1900-01-01'::date,CURRENT_DATE,interval '1 day') AS s;
 count 
-------
  5854
(1 row)

-- Final solution:
CREATE OR REPLACE FUNCTION first_of_week(date) returns date AS $$
  SELECT ($1::date-(extract('dow' FROM $1::date)*interval '1 day'))::date;
$$ LANGUAGE SQL STABLE STRICT;

CREATE OR REPLACE FUNCTION weeks_in_range(date,date) returns int AS $$
  SELECT ((first_of_week($2)-first_of_week($1))/7)+1
$$ LANGUAGE SQL STABLE STRICT;

EXPLAIN ANALYZE SELECT weeks_in_range('1900-01-01',CURRENT_DATE);
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.04 rows=1 width=0) (actual time=0.014..0.015 rows=1 loops=1)
 Total runtime: 0.030 ms
(2 rows)

SELECT weeks_in_range('1900-01-01',CURRENT_DATE);
 weeks_in_range 
----------------
           5854
(1 row)

SELECT weeks_in_range('2017-07-24', '2017-07-30') should be 1 but result 2. why?

SELECT weeks_in_range('2017-07-24', '2017-07-30') should be 1 but result 2. why?

Because first_of_week assumes Sunday to be the start of the week.

If you want to assume that Monday is the start of the week, then use date_trunc, e.g.

CREATE OR REPLACE FUNCTION weeks_in_range(date,date) returns int AS $$
  SELECT (((EXTRACT(DAY FROM (date_trunc('week', $2::date) - date_trunc('week', $1::date))))/7)+1)::int
$$ LANGUAGE SQL STABLE STRICT;

SELECT weeks_in_range('2018-07-23', '2018-07-29');