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
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...
Show quoted text
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?
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''''::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
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