Re: definative way to place secs from epoc into timestamp - Mailing list pgsql-sql

On Wed, 2005-03-02 at 14:26, Tom Lane wrote:
> Bret Hughes <bhughes@elevating.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