[erlang-questions] timestamp for pgsql

Dave Rafkind dave.rafkind@REDACTED
Wed Dec 5 18:37:16 CET 2007

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
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

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]

Eshell V5.5.5  (abort with ^G)

1> {ok, Db} = pgsql:connect("localhost", "tracker", "dave", "").
Params: [{secret,{5521,1197959615}},
         {{parameter,"DateStyle"},"ISO, MDY"},
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:

** exited: {{badrecord,dict},
             {pgsql_proto,idle,2}]} **

3> pgsql:prepare(Db, insert_point, "insert into my_table (timestamp_column)
4> pgsql:pquery(Db, "insert into my_table (timestamp_column) values ($1)",
5> pgsql:pquery(Db, "insert into test_gps (device_id, speed, heading) values
($1,$2,$3)", ["abc", 45,

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>

More information about the erlang-questions mailing list