Обсуждение: 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''''::intervalAS "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

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
> 

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

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''''::intervalAS "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

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