Re: [HACKERS] getting at the actual int4 value of an abstime

On Tue, Aug 17, 1999 at 06:23:29PM -0400, Jim Mercer wrote:
>
> i have a table which uses an abstime to store a time/date.
>
> the data originates as unix time_t, which i convert to a string when inserting
> the data into the table.
>
> i do select's from the table with WHERE clauses that use the abstime stuff.
>
> i want to get the results of a select as unix time_t, without having to use
> the expensive mktime()/strptime() unix C calls.
>
> is there a way to get the int4 value that postgres is storing raw for
> abstime?

test=> create table timetest(timefield abstime);
CREATE
test=> select abstime_finite(timefield) from timetest;
abstime_finite
--------------
(0 rows)

test=> insert into timetest values (now());
INSERT 518323 1
test=> insert into timetest values (now());
INSERT 518324 1
test=> insert into timetest values (now());
INSERT 518325 1
test=> select abstime_finite(timefield) from timetest;
abstime_finite
--------------
t
t
t
(3 rows)

test=> select timefield from timetest;
timefield
----------------------------
Tue Aug 17 18:13:23 1999 CDT
Tue Aug 17 18:13:24 1999 CDT
Tue Aug 17 18:13:25 1999 CDT
(3 rows)

test=> select timefield::int4 from timetest;
?column?
----------------------------
Tue Aug 17 18:13:23 1999 CDT
Tue Aug 17 18:13:24 1999 CDT
Tue Aug 17 18:13:25 1999 CDT
(3 rows)

Hmm, this looks like a bug. I'm guessing we're storing and int8, and the
conversion fails, so falls back to the default text output?

test=> select timefield::int8 from timetest;
     int8
---------
934931603
934931604
934931605
(3 rows)

test=> select timefield::float from timetest;
   float8
---------
934931603
934931604
934931605
(3 rows)

test=> select timefield::numeric from timetest;
  numeric
---------
934931603
934931604
934931605
(3 rows)

test=>

What version of PostgreSQL, BTW? This is 6.5: int8 and numeric support got a
lot better vs. 6.4

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