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



On Thu, Aug 16, 2001 at 08:53:26PM +0200, Ben-Nes Nimrod wrote:
> Howdie everyone
> how can i use unix timestamp as a data type?
> i thought that the timestamp data type use the unix timestamp but found out that it didnt.. :(
> 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?
> thxy
>

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