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