From: Bret Hughes <bhughes@elevating.com>
To: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: definative way to place secs from epoc into timestamp
Date: 02 Mar 2005 23:42:37 -0600
Message-ID: <1109828560.27905.137.camel@bretsony> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bhughes@elevating.com
Subject: Re: definative way to place secs from epoc into timestamp
In-Reply-To: <1109828560.27905.137.camel@bretsony>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox