Re: epoch to timestamp

Follow the patterns in src/backend/utils/adt/timestamp.c
for external functions--hint: they have PG_FUNCTION_ARGS
as their parameter.

Using the monkey see, monkey do method of coding:
float -> time_t -> tm -> timestamp

Datum
epoch2timestamp(PG_FUNCTION_ARGS)
{
Timestamp dt;
time_t t;
struct tm *t0;
float8 *f = PG_GETARG_FLOAT8(0);

t = /* Populate the time_t struct with epoch value in *f */
t0 = gmtime( &t );
tm2timestamp(tm, 0, NULL, &dt);

PG_RETURN_TIMESTAMP( &dt );

}

Put the function in timestamp.c.
Do whatever one does to register the function for
template1. I'm not sure how postgres sets this up
for built-in functions, but you need to create
the pg_proc row for the function.

This was coded in email so it won't really work.
And I'm sure someone could make it keener...
It is just a hint as to where to start.

elein

On Wed, Aug 20, 2003 at 12:09:16PM -0700, David Fetter wrote:
> On Wed, Aug 20, 2003 at 10:51:01AM -0700, elein wrote:
> > Someone tell me the easier, softer way to convert an epoch into a
> > timestamp.
>
> > This is the ugly way:
> > select ('1/1/1970'::timestamp + '968190450 seconds')::timestamp;
>
> Similar guts below...
>
> On hackers, Tom Lane proposed a function that does this using Pl/PgSQL
> like this:
>
> CREATE OR REPLACE FUNCTION epoch_to_timestamp (FLOAT8)
> RETURNS timestampz AS '
> BEGIN
> RETURN ''epoch''::timestamptz + $1 * ''1 second''::interval;
> END;
> ' LANGUAGE 'plpgsql';
>
> Despite the fact that it's (in hindsight) trivial to do, I'm still
> lobbying for making this a built-in function. Any pointers on how to
> do this in C & roll it into the tree?
>
> Cheers,
> D
> --
> David Fetter david(at)fetter(dot)org http://fetter.org/
> phone: +1 510 893 6100 cell: +1 415 235 3778
>