Re: [HACKERS] getting at the actual int4 value of an abstime - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [HACKERS] getting at the actual int4 value of an abstime
Date
Msg-id 7430.935780757@sss.pgh.pa.us
Whole thread Raw
In response to Re: [HACKERS] getting at the actual int4 value of an abstime  (Thomas Lockhart <lockhart@alumni.caltech.edu>)
Responses Re: [HACKERS] getting at the actual int4 value of an abstime
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] Performance of MIN() and MAX()
Next
From: jim@reptiles.org (Jim Mercer)
Date:
Subject: Re: [HACKERS] getting at the actual int4 value of an abstime