Skip site navigation (1) Skip section navigation (2)

Re: epoch to timestamp

From: elein <elein(at)varlena(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: epoch to timestamp
Date: 2003-08-20 20:46:00
Message-ID: 20030820134600.M13446@cookie (view raw or flat)
Thread:
Lists: sfpug
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
> 

In response to

sfpug by date

Next:From: Roger SmithDate: 2003-08-21 16:42:47
Subject: Bay Area startup has one position open for senior DB developer
Previous:From: David FetterDate: 2003-08-20 19:09:16
Subject: Re: epoch to timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group