Postgresql date subtract

Question

How can I make a valid request like:

UPDATE b2c SET tranche = '18 - 25'
WHERE (dateofbirth::date BETWEEN NOW()::date - 18 'year' AND NOW()::date - 25 'year')

Thanks for help

Clodoaldo Neto · Accepted Answer · 2015-10-12 10:36:28Z

1

dateofbirth::date BETWEEN 
    (NOW() - interval '25 year')::date AND 
    (NOW() - interval '18 year')::date

answered Oct 12, 2015 at 10:06

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, but I have 'nothink update', however I have in 'dateofbirth' some date like 1995-08-04, 1994-08-07, 1993-11-11...

sorry, I just had to switch 25 year with 18 year... I didn't know order of BETWEEN argument have an importance...

@Macbernie Fixed. The dates were inverted. It is the oldest first.

Vivek S. · Accepted Answer · 2015-10-12 10:42:43Z

1

select EXTRACT(year FROM age('1995-08-04'::date))::int age

Output:

age
integer
--------
      20

So you can write where condition like below

UPDATE b2c SET tranche = '18 - 25'
WHERE EXTRACT(year FROM age(dateofbirth))::int >=18 
 AND  EXTRACT(year FROM age(dateofbirth))::int <=25

answered Oct 12, 2015 at 10:27

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

klin · Accepted Answer · 2015-10-13 11:12:43Z

1

You can use Postgres cast syntax:

UPDATE b2c SET tranche = '18 - 25'
WHERE dateofbirth::date BETWEEN 
    NOW()::date - '25y'::interval AND 
    NOW()::date - '18y'::interval

answered Oct 12, 2015 at 10:06

klin

123k15 gold badges240 silver badges262 bronze badges