Skip site navigation (1) Skip section navigation (2)

Re: [SQL] how to cast localtimestamp to bigint???

From: george young <gry(at)ll(dot)mit(dot)edu>
To: "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [SQL] how to cast localtimestamp to bigint???
Date: 2004-08-13 17:34:57
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novicepgsql-sql
On Thu, 12 Aug 2004 07:47:06 -0700
"Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)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 -

You could use(from

  EXTRACT (field FROM source)

    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


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.


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)

In response to

pgsql-novice by date

Next:From: Stephan SzaboDate: 2004-08-13 17:43:39
Subject: Re: PGSQL Crossroads
Previous:From: Josh BerkusDate: 2004-08-13 17:27:07
Subject: Re: PGSQL Crossroads

pgsql-sql by date

Next:From: Tom LaneDate: 2004-08-13 17:54:12
Subject: Re: Wierd Error on update
Previous:From: Josh BerkusDate: 2004-08-13 17:19:15
Subject: Re: [PERFORM] Performance Problem With Postgresql!

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group