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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Friday, March 11, 2011 8:15 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11 March 2011 14:14, Alexander Farber <alexander.farber@gmail.com> wrote:

# 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?

Hi,
try this:

to_timestamp(u.created)

regards
Szymon

On Mar 11, 2011, at 6:44 PM, Alexander Farber wrote:

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

Use to_timestamp function. For more detail refer following documentation:
http://www.postgresql.org/docs/8.3/static/functions-formatting.html

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com