Re: [SQL] how to cast localtimestamp to bigint??? - Mailing list pgsql-novice

From Pradeepkumar, Pyatalo (IE10)
Subject Re: [SQL] how to cast localtimestamp to bigint???
Date
Msg-id 77ED2BF75D59D1439F90412CC5B109741054606A@ie10-sahara.hiso.honeywell.com
Whole thread Raw
List pgsql-novice
Hi all,

Thanx for all your valuable inputs. I have tried all the suggestions....they
seem to be working fine....Thanks a lot.



-----Original Message-----
From: george young [mailto:gry@ll.mit.edu]
Sent: Friday, August 13, 2004 11:05 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] [SQL] how to cast localtimestamp to bigint???


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)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

pgsql-novice by date:

Previous
From: "Davis, Sean (NIH/NHGRI)"
Date:
Subject: Re: New coloumns
Next
From:
Date:
Subject: PGSQL 7.4.3 Compatible with 7.4.2?