Thread: 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
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