Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



You need to determine how the integer value in "created" in calculated and
massage either it and/or "now()" into the same format so that you can
compare and manipulate them.  There is likely no simple CAST expression you
can use but instead have to perform math operations on the values.

Since created does appear to be a timestamp field if you have any control
I'd recommend changing it to be one and modify whatever program inputs that
value so that it uses actual dates instead of what appear to be second or
milliseconds since some epoch time.

David J.

-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber
Sent: Friday, March 11, 2011 8:15 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject:  Compare an integer to now() - interval '3 days'

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux