pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
now() returning int4
3+ messages / 3 participants
[nested] [flat]

* now() returning int4
@ 2000-01-31 16:59 Marc Tardif <admin@wtbwts.com>
  2000-01-31 22:28 ` Re: [SQL] now() returning int4 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Marc Tardif @ 2000-01-31 16:59 UTC (permalink / raw)
  To: pgsql-sql

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

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

Marc




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] now() returning int4
  2000-01-31 16:59 now() returning int4 Marc Tardif <admin@wtbwts.com>
@ 2000-01-31 22:28 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  2000-02-01 02:13   ` Re: [SQL] now() returning int4 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Ross J. Reedstrom @ 2000-01-31 22:28 UTC (permalink / raw)
  To: Marc Tardif <admin@wtbwts.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] now() returning int4
  2000-01-31 16:59 now() returning int4 Marc Tardif <admin@wtbwts.com>
  2000-01-31 22:28 ` Re: [SQL] now() returning int4 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
@ 2000-02-01 02:13   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-02-01 02:13 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: Marc Tardif <admin@wtbwts.com>; pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-02-01 02:13 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-31 16:59 now() returning int4 Marc Tardif <admin@wtbwts.com>
2000-01-31 22:28 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-02-01 02:13   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox