[erlang-questions] timestamp for pgsql

[Dave Rafkind]

I was hoping there was a way to give a timestamp from erlang using the
output of erlang:now() but thanks for the information about strings.
Also I'm using prepare/execute because I assume it is faster than pquery and
because
pquery hangs/timeouts for the queries I like to do.


I'll put this info in the jungerl bug tracker on sourceforge also:

For pgsql from jungerl (checked out Dec 3 2007), using PostGres 8.0.13/PostGis
1.1.4

psql> create table my_table (timestamp_column timestamp);
psql> create table test_gps (message_id  BIGSERIAL,
  occurrence  TIMESTAMP,
  device_id   VARCHAR,
  location    GEOMETRY,
  speed       REAL,
  heading     REAL,
  info VARCHAR);


Then in erlang using jungerl's pgsql:

Erlang (BEAM) emulator version 5.5.5 [source] [async-threads:0] [hipe]
[kernel-poll:false]

Eshell V5.5.5  (abort with ^G)

1> {ok, Db} = pgsql:connect("localhost", "tracker", "dave", "").
Params: [{secret,{5521,1197959615}},
         {{parameter,"TimeZone"},"US/Eastern"},
         {{parameter,"session_authorization"},"dave"},
         {{parameter,"server_version"},"8.0.13"},
         {{parameter,"server_encoding"},"SQL_ASCII"},
         {{parameter,"is_superuser"},"off"},
         {{parameter,"integer_datetimes"},"off"},
         {{parameter,"DateStyle"},"ISO, MDY"},
         {{parameter,"client_encoding"},"SQL_ASCII"}]
{ok,<0.33.0>}
2> pgsql:prepare(Db, insert_point, "insert into test_gps (device_id, speed,
heading, info, location) values ($1,$2,$3,$4,GeomFromText($5,-1))").

=ERROR REPORT==== 5-Dec-2007::12:09:08 ===
Error in process <0.33.0> with exit value:
{{badrecord,dict},[{dict,get_slot,2},{dict,fetch,2},{lists,map,2},{pgsql_proto,idle,2}]}

** exited: {{badrecord,dict},
            [{dict,get_slot,2},
             {dict,fetch,2},
             {lists,map,2},
             {pgsql_proto,idle,2}]} **

3> pgsql:prepare(Db, insert_point, "insert into my_table (timestamp_column)
values
($1)").
timeout
4> pgsql:pquery(Db, "insert into my_table (timestamp_column) values ($1)",
["2000-01-01
12:43:23"]).
timeout
5> pgsql:pquery(Db, "insert into test_gps (device_id, speed, heading) values
($1,$2,$3)", ["abc", 45,
54]).
timeout


NOTE: The pgsql:prepare and a subsequent pgsql:execute works in the pgsql
from ejabberd-modules. The pgsql:pquery calls always time out. pgsql:squery
calls usually work though.



On Dec 5, 2007 8:41 AM, Daniel Caune <daniel.caune@REDACTED> wrote:

> >In the current postgresql driver for erlang (I'm using the
> ejabberd-module >one because that's the one I got to work), how does one
> specify TIMESTAMP >values?
> >
> >ie if I do
> >
> >pgsql:prepare(Db, my_insert, "insert into foo_table (timestamp_column)
> >values ($1)").
> >
> >what should I put in the subsequent call to
> >
> >pgsql:execute(Db, my_insert, [?????])
> >
>
> You should be able to write something like this:
>
> pgsql:prepare(Db, my_insert, "insert into foo_table (timestamp_column)
> values ($1::timestamp)").
> pgsql:execute(Db, my_insert, ["YYYY-MM-DD HH24:MN:SS"]).
>
> I'm not sure that "::timestamp" is very useful as I suppose PostgreSQL
> to do the conversion by its own.
>
> >P.S. I think the Junglerl pgsql driver is broken for prepared
> statements.
>
> Did you try pgsql:pquery?  It works fine.
>
> --
> Daniel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20071205/2263b7c7/attachment.htm>