pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to cast localtimestamp to bigint???
4+ messages / 4 participants
[nested] [flat]
* how to cast localtimestamp to bigint???
@ 2004-08-12 14:47 Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-08-12 16:58 ` Re: [SQL] how to cast localtimestamp to bigint??? Edmund Bacon <ebacon@onesystem.com>
2004-08-13 14:44 ` Re: [SQL] how to cast localtimestamp to bigint??? Guy Fraser <guy@incentre.net>
2004-08-13 17:34 ` Re: [SQL] how to cast localtimestamp to bigint??? george young <gry@ll.mit.edu>
0 siblings, 3 replies; 4+ messages in thread
From: Pradeepkumar, Pyatalo (IE10) @ 2004-08-12 14:47 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
Hi,
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....
> With Best Regards
> Pradeep Kumar P J
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] how to cast localtimestamp to bigint???
2004-08-12 14:47 how to cast localtimestamp to bigint??? Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
@ 2004-08-12 16:58 ` Edmund Bacon <ebacon@onesystem.com>
2 siblings, 0 replies; 4+ messages in thread
From: Edmund Bacon @ 2004-08-12 16:58 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-novice@postgresql.org
I would have expected
SELECT current_timestamp::abstime::bigint;
to have worked, but there is no conversion abstime -> bigint.
Instead use
SELECT current_timestamp::abstime::int::bigint;
The final cast to bigint is not strictly neccessary.
This all begs the question, though, of why you are using a bigint for
createtime and not a timestamp?
Pradeepkumar, Pyatalo (IE10) wrote:
> Hi,
>
> 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....
>
>
>
>>With Best Regards
>>Pradeep Kumar P J
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] how to cast localtimestamp to bigint???
2004-08-12 14:47 how to cast localtimestamp to bigint??? Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
@ 2004-08-13 14:44 ` Guy Fraser <guy@incentre.net>
2 siblings, 0 replies; 4+ messages in thread
From: Guy Fraser @ 2004-08-13 14:44 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-novice@postgresql.org
You don't need to use a bigint, read the documentation on data types.
There are a number of different things you can use for a timestamp, here
are a few :
abstime
abstime with timezone
timestamp
timestamp with timezone
Then when you are inserting your data, you can use 'now' as the time.
Eg.
CREATE TABLE pointy_stuff (
PointId integer,
PointName varchar(50),
PointType integer,
CreateTime abstime
);
INSERT INTO pointy_stuff (
PointId,
PointName,
PointType,
CreateTime
) VALUES (
'12345',
'point1',
'1',
'now'
);
I believe abstime is the smallest timestamp, but I could be wrong.
To output the data as a bigint I believe you can use this.
SELECT
PointId,
PointName,
PointType,
date_part('epoch',CreateTime) as unix_ts
FROM
pointy_stuff
;
Hope that helps.
Pradeepkumar, Pyatalo (IE10) wrote:
>Hi,
>
>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....
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] how to cast localtimestamp to bigint???
2004-08-12 14:47 how to cast localtimestamp to bigint??? Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
@ 2004-08-13 17:34 ` george young <gry@ll.mit.edu>
2 siblings, 0 replies; 4+ messages in thread
From: george young @ 2004-08-13 17:34 UTC (permalink / raw)
To: Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>; +Cc: pgsql-novice@postgresql.org
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-08-13 17:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-12 14:47 how to cast localtimestamp to bigint??? Pradeepkumar, Pyatalo (IE10) <Pradeepkumar.Pyatalo@honeywell.com>
2004-08-12 16:58 ` Edmund Bacon <ebacon@onesystem.com>
2004-08-13 14:44 ` Guy Fraser <guy@incentre.net>
2004-08-13 17:34 ` george young <gry@ll.mit.edu>
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