Обсуждение: 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 ]
At 16:33 +0300 on 18/08/1999, Jim Mercer wrote:
> i suspect this would be more efficient than date_part('epoch', timefield).
Yes, but if someday someone decides that dates should be represented in
another way, this will break, and date_part( 'epoch', timefield ) will
always return the seconds since epoch. Data encapsulation thingie.
> also, is there a reverse to this?
>
> ie. how does one inject unix time_t data into an abstime field.
Into a datetime, simply use datetime( n ). To an abstime, add an abstime()
around the former. Don't try abstime( n ) - at least it doesn't work in 6.4.
> 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');"
copy is more efficient that a bunch of inserts, mind you.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
I feel like such a bone-head asking this question, but I didn't find the answer in the FAQ or the documentation, other than pgaccess is supposed to have some of this functionality... How do I import/export comma delimited tables? I thought a combination of pg_dump and psql might do it, but if so I must have missed it. I saw a mention of it for pgaccess, but I'm looking for something I can put in a shell script. -- Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
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
There is a COPY command that you can use...there is a man page for it,
sorry, don't use it myself, so dont know the syntax :( I've never had
much luck with using it, so generally cheat and create a fast perl script
to do it as normal inserts :(
On Wed, 18 Aug 1999, Bruce Tong wrote:
> I feel like such a bone-head asking this question, but I didn't find the
> answer in the FAQ or the documentation, other than pgaccess is supposed to
> have some of this functionality...
>
> How do I import/export comma delimited tables?
>
> I thought a combination of pg_dump and psql might do it, but if so I must
> have missed it. I saw a mention of it for pgaccess, but I'm looking for
> something I can put in a shell script.
>
> --
>
> Bruce Tong | Got me an office; I'm there late at night.
> Systems Programmer | Just send me e-mail, maybe I'll write.
> Electronic Vision / FITNE |
> zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
>
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Wed, 18 Aug 1999, Bruce Tong wrote: > I feel like such a bone-head asking this question, but I didn't find the > answer in the FAQ or the documentation, other than pgaccess is supposed to > have some of this functionality... > > How do I import/export comma delimited tables? > > I thought a combination of pg_dump and psql might do it, but if so I must > have missed it. I saw a mention of it for pgaccess, but I'm looking for > something I can put in a shell script. > > -- > > Bruce Tong | Got me an office; I'm there late at night. If you're after changing the field separator, psql has a \f command. You could do something like: $ psql -e <dbname> < out.sql > dump where out.sql looks like: \f , \o -- some select statements go here SELECT foo FROM bar; -- EOF A method for importing would be similar. Simon. -- "Don't anthropomorphise computers - they don't like it." Simon Drabble It's like karma for your brain. simon@eskimo.com
At 17:14 +0300 on 18/08/1999, Bruce Tong wrote: > How do I import/export comma delimited tables? > > I thought a combination of pg_dump and psql might do it, but if so I must > have missed it. I saw a mention of it for pgaccess, but I'm looking for > something I can put in a shell script. It has nothing to do with pgaccess. The way to import/export any tables is using either the COPY command in PostgreSQL's SQL dialect, or the \copy command in psql. The difference between them is in where they look for the file to convert to/from. The COPY command is executed by the backend, and looks for a file in the backend's machine. The \copy looks on the client machine that runs the psql. Since, more often than not, this is the same machine, the best way to remember is that COPY is executed by the backend and therefore the file must be readable to the postgres superuser (or writable for an export), and \copy runs in the client, so it should be readable/writable to the one who runs the psql. COPY has an option to read the standard input instead of a file, which is how clients like psql are able to write things like \copy. You can use COPY FROM STDIN in shell scripts. COPY is better that \copy as it allows you to set a delimiter, which \copy does not - it always expects tabs. Anyway, this imports data from a file named "stam.txt" into the table "test5" of the database "testing": psql -c 'COPY test5 FROM stdin' testing < stam.txt The following exports the same table: psql -qc 'COPY test5 TO stdin' testing > stam.txt Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> There is a COPY command that you can use...there is a man page for it, > sorry, don't use it myself, so dont know the syntax. Ahh, COPY. All I really needed was the pointer. I remember skimming that one and concluding it wasn't what I wanted. I must have skimmed too fast as I was certain it wouldn't be in SQL since nothing turned up in my seach of "The Practical SQL Handbook" index. Thanks to all for the examples. -- Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
>> There is a COPY command that you can use...there is a man page for it,
>> sorry, don't use it myself, so dont know the syntax.
Then some bit about usually using Perl because of trouble getting COPY to
perform exactly right and then having to pay the price with slow inserts
instead of fast COPY (sorry, I overhastily deleted it). I'm pretty sure
Marc posted it (sorry about the cc if it wasn't you Marc)...
Yes I usually have a similar problem, especially with 'buggy' CVS file or
other delimited files that haven't been rigourously generated or with
handling of NULL fields etc.
I clean up the file with Perl but use this code to still use fast COPYs:
#/usr/local/bin/perl5
my $database='test';
open PGSQL, "|psql $database" or die "hey man, you crazy or what! I canny
open pipe psql $database!";
my $table='test';
print PGSQL "COPY $table from stdin;\n"; # First COPY
my $print_count=0; # Set counter to zero
while (<LIST>) { # Where list is a filehandle to your CVS/delimited file
# We go through the file line by line
# Clean-up each line
# And put each element in array @values
# In the order of the fields in the table definition
# And replacing NULLs with '\N' (inclusive of quotes)
print PGSQL join("\t",@values),"\n";
++$print_count;
if (!($print_count%50)) { # every fifty print
print PGSQL "\\.\n"; # close that batch of entries
print PGSQL "COPY $table from stdin;\n"; # start next batch
};
};
print PGSQL "\\.\n";
# we've printed a copy so worst that can happen is we copy in nothing!
# but we must print this at then end to make sure all entries are copied
close(LIST);
close(PGSQL);
I must say that it goes like the proverbial stuff off the shovel.
HTH,
Stuart.
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+
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
>
> ************