Peter Neu wrote:
Hello,
I have 2 tables:
In one I log the user name of a web site user like this:
Name Access time
"makost0001" " 2007-04-19 15:09:19"
"makost0001" " 2007-04-19 15:09:19"
In the other I have the user name his group and the expiry date of his
account.
Name Group Expiry date
"makost0001" "book" "2013-04-05 09:41:25.357677"
When the user accesses the web site for the first time I need to change the
expiry date
to < today > + 3 years.
Problem is I need to do this on a regular basis once a day to avoid table
locks. And also how do I tell the program to find the earliest access time
of a user? Like shown above there a multiple
entries for the same time & user because of the multiple requests. :o(
Should I have a field in the first table like <ignore this entry forever>
when an expiry date is already set?
I'm pretty new to db programming and especially to PostgreSQL. Can somebody
please help?
Once a day, just do something like this:
UPDATE user_expiry_dates
SET expiry_date = CURRENT_DATE + '3 years'::interval
WHERE user_name IN (
SELECT user_name
FROM access_logs
GROUP BY user_name
HAVING min(access_time) BETWEEN (CURRENT_DATE - '1 day'::interval)
AND CURRENT_DATE)
);
However, if your expiry date was null before the user has visited the
site you might find it more efficient to do:
UPDATE user_expiry_dates
SET expiry_date = CURRENT_DATE + '3 years'::interval
WHERE
expiry_date IS NULL
AND user_name IN (
SELECT user_name
FROM access_logs
HAVING access_time BETWEEN (CURRENT_DATE - '1 day'::interval) AND
CURRENT_DATE)
);
Here we don't care if this user has logged in 100 times before today, we
only update the expiry_date if it's NULL.
HTH
--
Richard Huxton
Archonet Ltd
