Re: [HACKERS] getting at the actual int4 value of an abstime

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)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