How to solve 'ERROR: operator does not exist: '?

Question

I have a table created like this:

CREATE TABLE revinfo
(
   rev integer NOT NULL,
   revtstmp bigint,
   CONSTRAINT revinfo_pkey PRIMARY KEY (rev)
)

Inside this table, I have data like this:

rev  |revtstmp 
40815|1390021342972
40816|1390021401403
40817|1390021409057
40818|1390021409914
40819|1390021411074
40821|1390021463885
40822|1390021467889
40824|1390021469035
40826|1390021470065
40827|1390021472134
...

I want to list all revisions made after the 10th of February 2015. I tried this request:

select rev
from revinfo
where revtstmp > '2015-02-10 00:00:00'::timestamp

I hit this error:(see translation below)

ERREUR:  l'opérateur n'existe pas : bigint > timestamp without time zone
LINE 3: where revtstmp > '2015-02-12 00:00:00'::timestamp
                       ^
HINT:  Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
********** Erreur **********

ERREUR: l'opérateur n'existe pas : bigint > timestamp without time zone
État SQL :42883
Astuce : Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
Caractère : 47

How can I change my query ?

---

Translation (powered by Google Translate)

ERROR: operator does not exist: bigint> timestamp without time zone
LINE 3: where revtstmp> '2015-02-12 0:00:00' :: timestamp
^
HINT: No operator matches the given name and argument types.
You must add explicit type conversions.
********** ********** Error

ERROR: operator does not exist: bigint> timestamp without time zone
SQL State: 42883
Tip: No operator matches the given name and argument types.
You must add explicit type conversions.
Character: 47

Answer 1

Your column revtstmp is not a timestamp, so you cannot compare it to one.

Assuming that it is a "unix epoch" value, you can however convert a timestamp to a bigint quite easily:

select rev
from revinfo
where revtstmp > extract(epoch from '2015-02-10 00:00:00'::timestamp);

But in general it's better to store the information as a timestamp column, not as a bigint. That makes many queries a lot easier to write (and to read & understand)