Q:

How to update time of day in PostgreSQL

I have this statement
SELECT * 
FROM memberships 
WHERE EXTRACT(HOUR FROM expired_at) = 0 
  AND EXTRACT(DAY FROM expired_at) = 15;

I need to UPDATE all those records to be 08:00:00
So for example, if a record is 2016-06-15 00:00:00, I want to change it to 2016-06-15 08:00:00

A:

One way of doing this:
update memberships
   set expired_at = expired_at::date + time '08:00:00'
where extract(hour from expired_at) = 0
  and extract(day from expired_at) = 15

expired_at::date will create a date without a time and then the time is simply added.

