Kind people,

After discussing this w/Josh yesterday, I'd like to propose a new
function for date_time.  Here's the Pl/PgSQL version, warts 'n' all.

CREATE OR REPLACE FUNCTION epoch_to_timestamp (INTEGER)
RETURNS TIMESTAMP AS '
DECLARE
    the_sql  TEXT;
    the_record RECORD;
    the_time TIMESTAMP;
BEGIN
    the_sql := ''SELECT ''''January 1 1970''''::timestamp + '''''' 
             || $1
             || '' seconds''''::interval AS "foo"'';
    FOR the_record IN EXECUTE the_sql LOOP
        the_time := the_record.foo;
    END LOOP;
    RETURN the_time;
END;
' LANGUAGE 'plpgsql';

My C skills (Hello, world!) aren't up to doing it that way, although
that's probably The Right Thing(TM).  Is there a reasonable way to add
an optional param or two to mark time zones, or should that just the
application programmer's hassle?

TIA for any feedback :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778

Thu, 14 Aug 2003 13:46:31 -0700
David Fetter <david@fetter.org>
New function: epoch_to_timestamp...
You can actually kinda hack this by going:

SELECT 41235125::abstime::timestamp;

Where 41235125 is a unix epoch.

Cheers,

Chris

----- Original Message ----- 
From: "David Fetter" <david@fetter.org>
To: "PG Hackers" <pgsql-hackers@postgresql.org>
Sent: Friday, August 15, 2003 4:46 AM
Subject: [HACKERS] New function: epoch_to_timestamp...


> Kind people,
> 
> After discussing this w/Josh yesterday, I'd like to propose a new
> function for date_time.  Here's the Pl/PgSQL version, warts 'n' all.
> 
> CREATE OR REPLACE FUNCTION epoch_to_timestamp (INTEGER)
> RETURNS TIMESTAMP AS '
> DECLARE
>     the_sql  TEXT;
>     the_record RECORD;
>     the_time TIMESTAMP;
> BEGIN
>     the_sql := ''SELECT ''''January 1 1970''''::timestamp + '''''' 
>              || $1
>              || '' seconds''''::interval AS "foo"'';
>     FOR the_record IN EXECUTE the_sql LOOP
>         the_time := the_record.foo;
>     END LOOP;
>     RETURN the_time;
> END;
> ' LANGUAGE 'plpgsql';
> 
> My C skills (Hello, world!) aren't up to doing it that way, although
> that's probably The Right Thing(TM).  Is there a reasonable way to add
> an optional param or two to mark time zones, or should that just the
> application programmer's hassle?
> 
> TIA for any feedback :)
> 
> Cheers,
> D
> -- 
> David Fetter david@fetter.org http://fetter.org/
> phone: +1 510 893 6100    cell: +1 415 235 3778
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

Fri, 15 Aug 2003 10:12:25 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: New function: epoch_to_timestamp...
David Fetter <david@fetter.org> writes:
> Is there a reasonable way to add
> an optional param or two to mark time zones, or should that just the
> application programmer's hassle?

The return type should be timestamptz, which makes the transformation
timezone-independent.

			regards, tom lane

Fri, 15 Aug 2003 06:57:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New function: epoch_to_timestamp... 
On Fri, Aug 15, 2003 at 06:57:24AM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > Is there a reasonable way to add an optional param or two to mark
> > time zones, or should that just the application programmer's
> > hassle?

> The return type should be timestamptz, which makes the
> transformation timezone-independent.

Do you mean the default one should read as follows, or that the
default one should take params (integer, output_timezone,
input_timezone), or...?

CREATE OR REPLACE FUNCTION epoch_to_timestampz (INTEGER)
RETURNS TIMESTAMPZ AS '
DECLARE
    the_sql  TEXT;
    the_record RECORD;
    the_time TIMESTAMPZ;
BEGIN
    the_sql := ''SELECT ''''January 1 1970''''::timestamp + ''''''
             || $1
             || '' seconds''''::interval AS "foo"'';
    RAISE NOTICE ''%'', the_sql;
    FOR the_record IN EXECUTE the_sql LOOP
        the_time := the_record.foo;
    END LOOP;
    RETURN the_time;
END;
' LANGUAGE 'plpgsql';

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778

Fri, 15 Aug 2003 15:41:15 -0700
David Fetter <david@fetter.org>
Re: New function: epoch_to_timestamp...
David Fetter <david@fetter.org> writes:
> [code]

Seems pretty messy.  I believe the standard way of doing this is

regression=# create function epoch_to_timestamp(float8) returns timestamptz as '
regression'# begin
regression'#   return ''epoch''::timestamptz + $1 * ''1 second''::interval;
regression'# end' language plpgsql strict immutable;
CREATE FUNCTION

regression=# select now(), extract(epoch from now());
              now              |    date_part
-------------------------------+-----------------
 2003-08-16 08:43:16.925501-04 | 1061037796.9255
(1 row)

regression=# select epoch_to_timestamp(1061037796.9255);
     epoch_to_timestamp
-----------------------------
 2003-08-16 08:43:16.9255-04
(1 row)


			regards, tom lane

Sat, 16 Aug 2003 08:44:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New function: epoch_to_timestamp... 