Re: definative way to place secs from epoc into timestamp

On Wed, 2005-03-02 at 14:26, Tom Lane wrote:
> Bret Hughes <bhughes(at)elevating(dot)com> writes:
> > Thanks for the feed back tom I say that but I could not believe that I
> > have to jump through all those hoops on an insert or update
>
> > update mytable set (lasttime =(SELECT TIMESTAMP WITH TIME ZONE 'epoch' +
> > 982384720 * INTERVAL '1 second') )
>
> > is this what you are saying I need to do?
>
> You can make a function that embodies whichever semantics you want.
>
> > also, what is happening with abstime(982384720)? this works as expected
> > (by me ). Is this a bad idea?
>
> It won't be there forever.
>
Thanks again for the help Tom. My solution for those intrepid archive
searchers that follow:

also my first two pgsql functions :)

cat ts2int.sql
drop function int2ts(integer);
drop function ts2int(timestamp without time zone);

create function int2ts(integer) returns timestamp as '
SELECT ( TIMESTAMP WITH TIME ZONE \'epoch\' + $1 * INTERVAL \'1
second\')::timestamp without time zone;
' language sql;

create function ts2int(timestamp without time zone) returns int as '
select extract( \'epoch\' from $1)::integer;
' language sql;

comment on function int2ts(integer) is
'convert a unix timestamp based integer to a timestamp without time
zone';

comment on function ts2int(timestamp without time zone) is
'convert a timstamp without time zone to a unix timstamp based integer';
Thanks again for your patience as I try to get my head around how pg
handles this stuff. I am getting close to getting my head around it but
seem to have a block on picturing the internals.

Bret