Обсуждение: getting at the actual int4 value of an abstime
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? i'm working in C with libpq. -- [ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ] [ The telephone, for those of you who have forgotten, was a commonly used ] [ communications technology in the days before electronic mail. ] [ They're still easy to find in most large cities. -- Nathaniel Borenstein ]
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
> > i have a table which uses an abstime to store a time/date.
> > the data originates as unix time_t
> > 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?
postgres=> select date_part('epoch', timefield) from timetest;
date_part
---------
934957840
(1 rows)
> test=> select timefield::int4 from timetest;
> ?column?
> ----------------------------
> Tue Aug 17 18:13:23 1999 CDT
> 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?
Probably not. Abstime is internally stored as 4 bytes, roughly the
same as int4, and so Postgres is swallowing the conversion since it
thinks they are equivalent. But the output conversion is not
equivalent.
> test=> select timefield::int8 from timetest;
> int8
> ---------
> 934931603
> What version of PostgreSQL, BTW? This is 6.5: int8 and numeric support got a
> lot better vs. 6.4
Trying to force a conversion to some other data type works, since the
conversion isn't swallowed by Postgres. The int4 behavior should count
as a bug...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> test=> select timefield::int8 from timetest;
> int8
> ---------
> 934931603
> 934931604
> 934931605
> (3 rows)
hmmm, as you did, i tried timefield::int4, and got the same results.
i hadn't tried timefield::int8.
i suspect this would be more efficient than date_part('epoch', timefield).
> What version of PostgreSQL, BTW? This is 6.5: int8 and numeric support got a
> lot better vs. 6.4
i am using 6.5, soon gonna upgrade to 6.5.1.
thanx, this will make my code much more efficient.
also, is there a reverse to this?
ie. how does one inject unix time_t data into an abstime field.
i currently pass my raw data through a filter, which converts it
to 'yyyy-mm-dd HH:MM:SS'.
then i bring it in using: "COPY tb USING STDIN;"
it would be nice if i could do a batch of:
"INSERT INTO tb (time_t, data1, date2) VALUES (934931604, 'aa', 'bb');"
--
[ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]
jim@reptiles.org (Jim Mercer) writes:
> [ concern about speed of converting datetime values to/from text for
> Postgres ]
FWIW, I used to be really concerned about that too, because my
applications do lots of storage and retrieval of datetimes.
Then one day I did some profiling, and found that the datetime
conversion code was down in the noise. Now I don't worry so much.
It *would* be nice though if there were some reasonably cheap documented
conversions between datetime and a standard Unix time_t displayed as a
number. Not so much because of speed, as because there are all kinds
of ways to get the conversion wrong on the client side --- messing up
the timezone and not coping with all the Postgres datestyles are two
easy ways to muff it.
BTW, I believe Thomas is threatening to replace all the datetime-like
types with what is currently called datetime (ie, a float8 measuring
seconds with epoch 1/1/2000), so relying on the internal representation
of abstime would be a bad idea...
regards, tom lane
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>>>> 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?
> postgres=> select date_part('epoch', timefield) from timetest;
> date_part
> ---------
> 934957840
> (1 rows)
BTW, while rooting around in contrib/ I noticed that contrib/unixdate
has an efficient way of going the other direction: just apply the
conversion from abstime with a type cheat. The coding is obsolete,
but updated to 6.5, it works fine:
regression=> CREATE FUNCTION datetime(int4) RETURNS datetime
regression-> AS 'abstime_datetime' LANGUAGE 'internal';
CREATE
regression=> select datetime(935779244);
datetime
----------------------------
Fri Aug 27 14:40:44 1999 EDT
(1 row)
regression=> select date_part('epoch',
regression-> 'Fri Aug 27 14:40:44 1999 EDT'::datetime);
date_part
---------
935779244
(1 row)
Nifty. I wonder whether we shouldn't move this contrib feature into the
standard system for 6.6? Perhaps with a less generic name, such as
epoch2datetime() --- otherwise the parser will think that it can use the
function as an automatic int4->datetime type conversion, which is probably
Not a Good Idea. But having both conversion directions would sure make
life simpler and less error-prone for client apps that need to translate
datetimes to and from time_t.
regards, tom lane
> BTW, while rooting around in contrib/ I noticed that contrib/unixdate > has an efficient way of going the other direction: just apply the > conversion from abstime with a type cheat. The coding is obsolete, > but updated to 6.5, it works fine: i saw it there, but couldn't get it to work. this looks like what i need. -- [ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ] [ The telephone, for those of you who have forgotten, was a commonly used ] [ communications technology in the days before electronic mail. ] [ They're still easy to find in most large cities. -- Nathaniel Borenstein ]
You don't need to create such function it works already on v6.5:
prova=> select date_part('epoch', current_date);
date_part
---------
935964000
(1 row)
prova=> select datetime(935964000);
datetime
---------------------------
30/08/1999 00:00:00.00 CEST
(1 row)
prova=> select date_part('epoch','30/08/1999 00:00:00.00 CEST'::datetime);
date_part
---------
935964000
(1 row)
José
Tom Lane ha scritto:
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >>>> 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?
>
> > postgres=> select date_part('epoch', timefield) from timetest;
> > date_part
> > ---------
> > 934957840
> > (1 rows)
>
> BTW, while rooting around in contrib/ I noticed that contrib/unixdate
> has an efficient way of going the other direction: just apply the
> conversion from abstime with a type cheat. The coding is obsolete,
> but updated to 6.5, it works fine:
>
> regression=> CREATE FUNCTION datetime(int4) RETURNS datetime
> regression-> AS 'abstime_datetime' LANGUAGE 'internal';
> CREATE
> regression=> select datetime(935779244);
> datetime
> ----------------------------
> Fri Aug 27 14:40:44 1999 EDT
> (1 row)
> regression=> select date_part('epoch',
> regression-> 'Fri Aug 27 14:40:44 1999 EDT'::datetime);
> date_part
> ---------
> 935779244
> (1 row)
>
> Nifty. I wonder whether we shouldn't move this contrib feature into the
> standard system for 6.6? Perhaps with a less generic name, such as
> epoch2datetime() --- otherwise the parser will think that it can use the
> function as an automatic int4->datetime type conversion, which is probably
> Not a Good Idea. But having both conversion directions would sure make
> life simpler and less error-prone for client apps that need to translate
> datetimes to and from time_t.
>
> regards, tom lane
>
> ************