Compare an integer to now() - interval '3 days'

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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