Date: Fri, 13 Aug 2004 13:34:57 -0400
From: george young <gry@ll.mit.edu>
To: "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar.Pyatalo@honeywell.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: [SQL] how to cast localtimestamp to bigint???
Message-Id: <20040813133457.12f50ed5.gry@ll.mit.edu>
In-Reply-To: 
 <77ED2BF75D59D1439F90412CC5B109741027FF0F@ie10-sahara.hiso.honeywell.com>
References: 
 <77ED2BF75D59D1439F90412CC5B109741027FF0F@ie10-sahara.hiso.honeywell.com>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 12 Aug 2004 07:47:06 -0700
"Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar.Pyatalo@honeywell.com> threw this fish to the penguins:

> I am having a table something like this....
> 
> CREATE TABLE(PointId integer, PointName varchar(50),PointType integer,
> createtime bigint);
> 
> where createtime is the current timestamp when the tuple is inserted.
> 
> now how do I insert values into the above table. Is there a way to cast
> timestamp to bigint.
> Also can anyone suggest as to which date function to use -
> CURRENT_TIMESTAMP, LOCALTIMESTAMP, timeofday(), now....

You could use(from http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)

  EXTRACT (field FROM source)
  epoch

    For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (can be negative); for interval values, the total number of seconds in the interval

e.g.:

select CURRENT_TIMESTAMP, extract('epoch' from CURRENT_TIMESTAMP)::bigint;
(1 row)
          timestamptz          | date_part  
-------------------------------+------------
 2004-08-13 13:27:30.715408-04 | 1092418051

The bigint cast will round to the nearest second.


See:
  http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

for subtleties of various current time/date functions.

-- George Young
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)