Weeks between two dates in Postgres

Question

I want to calculate the number of weeks that a customer has been a customer. The goal is to compare this to the distinct number of weeks the customer has made a purchase to determine what % of weeks the customer made a purchase.

With timestamp customer.created_at what is the best way to find the number of weeks a customer has been a customer? Said differently, the difference in weeks between the current week and the week in which the customer signed up.

Calendar weeks starting Monday to be apples-to-apples with distinct calendar weeks a customer has made a purchase.

Answer 1

AFAIK this should work in all cases

-Your calendar week starts on Monday
-Both dates start at least from 05.01.1970 (Monday)

Postgres:

SELECT
(
extract(epoch from date_trunc('week',now()::date))
-
extract(epoch from date_trunc('week','2019-12-20'::date))
)
/604800

(where 604800 is the number of seconds in a week)

Answer 2

SELECT (EXTRACT(days FROM (now() - customer.created_at)) / 7)::int;

First find the difference between current time and customer's created_at time stamp. Then you tell Postgres to give you the difference in days and then you can divide that by 7 and round it to an integer. Note that if you do not want an integer, you can cast appropriately.