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???
Date: Fri, 13 Aug 2004 13:34:57 -0400
Message-ID: <20040813133457.12f50ed5.gry@ll.mit.edu> (raw)
In-Reply-To: <77ED2BF75D59D1439F90412CC5B109741027FF0F@ie10-sahara.hiso.honeywell.com>
References: <77ED2BF75D59D1439F90412CC5B109741027FF0F@ie10-sahara.hiso.honeywell.com>
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)
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: gry@ll.mit.edu, Pradeepkumar.Pyatalo@honeywell.com, pgsql-novice@postgresql.org
Subject: Re: [SQL] how to cast localtimestamp to bigint???
In-Reply-To: <20040813133457.12f50ed5.gry@ll.mit.edu>
* 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