Thread: now() returning int4

I have a table containing an integer field defaulting to now(), which I
want to use as the unix timestamp counting the seconds from jan 1 1970.
Problem is when I try to perform queries using now():

db=> CREATE TABLE test (
db-> date int default now()
db-> );
CREATE
db=> SELECT * FROM test WHERE date < now();
ERROR:  Unable to identify operator '<' for types 'int4' and 'timestamp'       You will have to retype this query using
anexplicit cast
 
db=> SELECT * FROM test WHERE date::timestamp < now();
bis
db=> SELECT * FROM test WHERE date < now()::int4;
bis

Please let me know how I can get this to work properly, I'm using
postgresql 6.5.3.

Marc

On Mon, Jan 31, 2000 at 04:59:19PM +0000, Marc Tardif wrote:
> I have a table containing an integer field defaulting to now(), which I
> want to use as the unix timestamp counting the seconds from jan 1 1970.
> Problem is when I try to perform queries using now():
> 
> db=> CREATE TABLE test (
> db-> date int default now()
> db-> );
> CREATE
> db=> SELECT * FROM test WHERE date < now();
> ERROR:  Unable to identify operator '<' for types 'int4' and 'timestamp'
>         You will have to retype this query using an explicit cast
> db=> SELECT * FROM test WHERE date::timestamp < now();
> bis
> db=> SELECT * FROM test WHERE date < now()::int4;
> bis
> 

SELECT * FROM test WHERE date < int(now());

Works here.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
>> db=> SELECT * FROM test WHERE date < now()::int4;

> SELECT * FROM test WHERE date < int(now());
> Works here.

I believe this is a bug in the typecasting code --- when you try to
cast a value to another type that the system considers bit-compatible
with the original type, the parser knows that no actual type conversion
work is required.  But it then decides it doesn't have to do *anything*,
and effectively drops the cast entirely.  It should be marking the
expression result as being of the destination type so that subsequent
processing works as the user intends.

Related example:

regression=# select now()::int4;       ?column?
------------------------2000-01-31 21:07:36-05
(1 row)

regression=# select int(now());   int
-----------949370865
(1 row)

Both of these should yield int-formatted output, IMHO.

I have this on my to-fix list for 7.0.
        regards, tom lane