Thread: converting epoch to timestamp
Hi, Can anyone tell me how to convert epoch to timestamp ? ie reverse of : SELECT EXTRACT( epoch FROM now() ); +------------------+ | date_part | +------------------+ | 1130317518.61997 | +------------------+ (1 row) Regds mallah.
am 26.10.2005, um 14:35:51 +0530 mailte Rajesh Kumar Mallah folgendes: > Hi, > > Can anyone tell me how to convert epoch to timestamp ? > > ie reverse of : > > SELECT EXTRACT( epoch FROM now() ); > +------------------+ > | date_part | > +------------------+ > | 1130317518.61997 | > +------------------+ > (1 row) SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1130317518.61997 * INTERVAL '1 second'; HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
Rajesh Kumar Mallah wrote: > Hi, > > Can anyone tell me how to convert epoch to timestamp ? > > ie reverse of : > > SELECT EXTRACT( epoch FROM now() ); I'd start with either Google or the manuals. http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html Scroll down to the section on "epoch" here and see the example. -- Richard Huxton Archonet Ltd
Rajesh Kumar Mallah mentioned : => Can anyone tell me how to convert epoch to timestamp ? => => ie reverse of : => => SELECT EXTRACT( epoch FROM now() ); => +------------------+ => | date_part | => +------------------+ => | 1130317518.61997 | => +------------------+ Here is one way (In my case I still had to add/subtract timezone diff) select '19700101'::timestamp + foo.epoch::interval from (select extract(epoch from now())||' seconds' as epoch) foo ;
Hi I am having some problem with function that returns SETOF RECORD
Here is my function:
CREATE OR REPLACE FUNCTION test_record(text) RETURNS SETOF RECORD AS
$BODY$
DECLARE
p_table_name ALIAS FOR $1;
temp_rec RECORD;
v_query text;
BEGIN
v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
LOOPRETURN NEXT temp_rec;
END LOOP;
RETURN ;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
And here is how I execute the function:
select * from test_record('field_list')
I have this error:
ERROR: a column definition list is required for functions returning
"record"
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <cpc@cybees.com> wrote:
> Hi I am having some problem with function that returns SETOF RECORD
>
> Here is my function:
>
> CREATE OR REPLACE FUNCTION test_record(text)
> RETURNS SETOF RECORD AS
> $BODY$
>
>
> DECLARE
> p_table_name ALIAS FOR $1;
> temp_rec RECORD;
> v_query text;
>
> BEGIN
>
> v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
> LOOP
> RETURN NEXT temp_rec;
> END LOOP;
>
> RETURN ;
>
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> And here is how I execute the function:
> select * from test_record('field_list')
>
> I have this error:
>
> ERROR: a column definition list is required for functions returning
> "record"
Since Postgres doesn't know what to expect from your function, you have to
tell it by giving the list of columns that are actually returned:
select * from test_record('field_list') as s(a,b,c,d)
where a,b,c,d are the columns in your returned set. (ie., in your example,
if p_table_name has 5 columns, you would use "as s(a,b,c,d,e)", etc.).
See here for more detail:
http://techdocs.postgresql.org/guides/SetReturningFunctions
Sean
On 10/26/05, Richard Huxton <dev@archonet.com> wrote: > Rajesh Kumar Mallah wrote: > > Hi, > > > > Can anyone tell me how to convert epoch to timestamp ? > > > > ie reverse of : > > > > SELECT EXTRACT( epoch FROM now() ); > > I'd start with either Google or the manuals. > > http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html Firstly Thanks everyone for the response. I did read this document but not too carefully hence missed. I missed because i was not careful and partly because i was not expecting that little note to be under documentation of EXTRACT which deals with getting date/time sub fields. I am no documentation expert just trying to explain why i could not find it. PS: sorry for late reply Regds Mallah. > > Scroll down to the section on "epoch" here and see the example. > > -- > Richard Huxton > Archonet Ltd >