Alexander Farber Alexander Farber - 1 year ago 73
SQL Question

Adding 1 week to a timestamp

In PostgreSQL 8.4 I have a user table:

# \d pref_users;
Table "public.pref_users"
Column | Type | Modifiers
id | character varying(32) | not null
first_name | character varying(64) |
last_name | character varying(64) |
female | boolean |
vip | timestamp without time zone |
mail | character varying(254) |

For users who have purchased VIP status I set

update pref_users set vip = now() + interval '6 month' where id='12345';

and later in other scripts I just check for it with

select vip > now() as vip_is_active from pref_users where id='12345';

But there are also 1) users who never purchased VIP (have vip = NULL) and 2) users whose VIP status has already expired (they have vip < now())

My question: as a promotion I'd like to give everyone 1 week VIP status.

I was hoping to do:

update pref_users set vip = max(vip, now()) + interval '1 week';

but max() doesn't work with timestamps.

Does anybody please have an idea, how to add 1 week to everyone's vip column?

Answer Source

Try this:

UPDATE pref_users SET vip = GREATEST(vip, now()) + interval '1 week';

max() works with timestamps just fine. But you are mixing query levels in a way that is not feasible. You want the function greatest(), not the aggregate function max().

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download