unix timestamp

You can insert unix timestamp into say
abstime field and it will automatically
be converted. This way you can manipulate
the timestamp outside anyway you want
and then insert into pg

how can i use unix timestamp as a data type?

You don't want to. Really.

i thought that the timestamp data type use the unix timestamp but
found out that it didnt.. :(

Right. One second resolution and limited range was considered a problem.

do i need to use an int data type for holding unix timestamp or there
is a data type which was build specialy for that?

I would suggest using the native timestamp type. You can convert back
and forth using various techniques, but you should find that the
examples below give you a good start:

lockhart=# select date_part('epoch',timestamp 'now');
date_part
-----------
997993780

lockhart=# select timestamp(integer '997993780');
timestamp
------------------------
2001-08-16 20:29:40+00

hth

- Thomas

how can i use unix timestamp as a data type?

the datetime data type should work. you can find info about it at

http://www.postgresql.org/idocs/index.php?functions-datetime.html
http://www.postgresql.org/idocs/index.php?datatype-datetime.html

here is some example code

CREATE TABLE test (
id integer PRIMARY KEY,
mytime datetime
);

#include <time.h>
...
time_t mytime = time (NULL);
printf ("time to be inserted into database: %s\n", ctime (&mytime));

/* insert row setting the time. if you want to insert the current time you
can pass 'now' to a datetime field */
sprintf (sql_str, "INSERT INTO test (id, mytime) VALUES (1, '%s')", ctime
(&mytime));
res = PQexec(dbconn, sql_str);
...

/* get row extracting the time since the epoch */
sprintf (sql_str, "SELECT id, extract (epoch from mytime) FROM test WHERE
oid = %d", PQoidValue(res));
...
mytime = atoi(PQgetvalue(res, 0, 1));

printf ("time retrieved from database: %s\n", ctime(&mytime));