pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednow() returning int4
3+ messages / 3 participants
[nested] [flat]
* now() returning int4
@ 2000-01-31 16:59 Marc Tardif <admin@wtbwts.com>
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 22:28 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Marc Tardif <admin@wtbwts.com>
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-02-01 02:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
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