Subject: Re: definative way to place secs from epoc into timestamp
From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
In-Reply-To: <13466.1109795186@sss.pgh.pa.us>
References: <1109780719.28180.86.camel@bretsony>
	<13089.1109793126@sss.pgh.pa.us> <1109794066.27857.100.camel@bretsony>
	<13466.1109795186@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 02 Mar 2005 23:42:37 -0600
Message-Id: <1109828560.27905.137.camel@bretsony>
Mime-Version: 1.0

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