Thread: 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