epoch to show millseconds

Mars G Miro <mars@cannoncreek.com> writes:

how do I make it accurate up to milliseconds value?

You don't. current_timestamp depends on the time() system call,
which only returns a number accurate to one second.

There is a higher-precision current time function in 7.1, but I
forget the details ... see the docs. IIRC it existed but was
buggy in 7.0, so you'll need to update.

regards, tom lane

Hi All

I've searched the archives, but it seems that there's no mention of this.
Basically, what I want to do is to show the milliseconds value.

FAQ: How can I select the time with milliseconds?

binary_data=# select date_part('millisecond', timeofday()::timestamp);
date_part
------------------
534.253999999997

A person asked this question a while back in #Postgresql on IRC EFNet.

On Wednesday 18 July 2001 09:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Mars G Miro <mars@cannoncreek.com> writes:

how do I make it accurate up to milliseconds value?

You don't. current_timestamp depends on the time() system call,
which only returns a number accurate to one second.

Well, I managed to have a workaround for it by:
punkdb=# select date_part('epoch', current_timestamp) ||
trim(to_char(date_part('millisecond', timeofday()::timestamp),'999'));

?column?
--------------
995418886281
(1 row)

There is a higher-precision current time function in 7.1, but I
forget the details ... see the docs. IIRC it existed but was
buggy in 7.0, so you'll need to update.

This is on 7.0.3

regards, tom lane

My sole purpose is to generate a primary key, w/c I believe is sufficient and
unique enough to avoid double records (or errors that an existing record of
the same epoch exist, if it's only up to the second).

Thanks, for the response though, I really appreciate it ;-)

cheers
mars
--
"I gained nothing at all from Supreme Enlightenment, and for that very
reason it is called Supreme Enlightenment."
-- Gotama Buddha

Remember that epoch is a number of seconds, plain and simple so you can't
get milliseconds from epoch, unless you wanted to carry zeros out to
infinity :-)

Good luck!

-Mitch

----- Original Message -----
From: "mars g miro" <mars@cannoncreek.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-sql@postgresql.org>;
<pgsql-general@postgresql.org>
Sent: Tuesday, July 17, 2001 9:22 PM
Subject: [GENERAL] Re: epoch to show millseconds

On Wednesday 18 July 2001 09:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

Mars G Miro <mars@cannoncreek.com> writes:

how do I make it accurate up to milliseconds value?

You don't. current_timestamp depends on the time() system call,
which only returns a number accurate to one second.

Well, I managed to have a workaround for it by:
punkdb=# select date_part('epoch', current_timestamp) ||
trim(to_char(date_part('millisecond', timeofday()::timestamp),'999'));

?column?
--------------
995418886281
(1 row)

There is a higher-precision current time function in 7.1, but I
forget the details ... see the docs. IIRC it existed but was
buggy in 7.0, so you'll need to update.

This is on 7.0.3

regards, tom lane

My sole purpose is to generate a primary key, w/c I believe is sufficient

and

unique enough to avoid double records (or errors that an existing record

of

mars g miro wrote:

My sole purpose is to generate a primary key, w/c I believe is sufficient and
unique enough to avoid double records

What's wrong with a SERIAL then?

--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750

"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925