| From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Compare an integer to now() - interval '3 days' |
| Date: | 2011-03-11 13:14:48 |
| Message-ID: | AANLkTi=BujjJXD0NxaEKhtNM5j9esYvuxH9huhu84vu2@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hello,
I've installed Drupal 7.0 on CentOS 5.5 + PostgreSQL 8.4.7
and have added a SPAM-trap - a field Gender which
can be Robot/Male/Female: http://preferans.de/user/register
Now I'm trying to delete all spammers, who haven't changed
the default value of Gender = Robot since at least 3 days:
# select u.uid, u.name, u.created
from drupal_field_data_field_gender g, drupal_users u
where g.field_gender_value='Robot' and u.uid=g.entity_id;
uid | name | created
------+------------------+------------
9740 | nevyCrannalon | 1299833046
9713 | DurnEffoneMof | 1299785537
9717 | trauptJaf | 1299786990
9720 | akop111 | 1299794072
9742 | ImmonoCiz | 1299838704
9744 | gchostin.cloudpd | 1299845470
9723 | OrdellAssausa | 1299797208
9725 | lelpbeelm | 1299798457
9726 | enlandendapef | 1299804072
9728 | teevyAnync | 1299809484
9731 | Gaspmaica | 1299821611
9736 | JamesSmith | 1299824762
(12 rows)
This works well, but I want to add the 3 days old condition:
# select u.uid, u.name, u.created
from drupal_field_data_field_gender g, drupal_users u
where g.field_gender_value='Robot' and
u.uid=g.entity_id and u.created::timestamp > now() - interval '1 day';
ERROR: cannot cast type integer to timestamp without time zone
LINE 4: u.uid=g.entity_id and u.created::timestamp > now() - interva...
What should I use instead of ::timestamp please?
Thank you
Alex
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Johnston | 2011-03-11 13:35:46 | Re: Compare an integer to now() - interval '3 days' |
| Previous Message | Bruce Momjian | 2011-03-11 12:10:15 | Re: Upgrading using streaming replication |