Thread: formatting timestamptz for more precision?

From what I've read, Postgres stores plenty of precision to contain
milliseconds.  My question is, how do I see the milliseconds?  Just doing a
select on a timestamptz column just gives me hh:mm:ss... but i'm looking
for something more like hh:mm:ss.ms.  Is there someway to do a format
within the select so that I can see the miliseconds as well as the rest of
the time?

--Chris

Christopher.Becker@apcc.com writes:
>> From what I've read, Postgres stores plenty of precision to contain
> milliseconds.  My question is, how do I see the milliseconds?  Just doing a
> select on a timestamptz column just gives me hh:mm:ss... but i'm looking
> for something more like hh:mm:ss.ms.

Well, you can't see precision that isn't there.  How are you inserting
data?

The only way the column itself would be throwing away low-order digits
is if you declared it to, with a declaration like "timestamp(0)".
Example:

regression=# create table foo(f1 timestamptz);
CREATE TABLE
regression=# insert into foo values(now());
INSERT 155060 1
regression=# select * from foo;
              f1
-------------------------------
 2004-06-21 21:44:47.344823-04
(1 row)

regression=# create table bar (f1 timestamp(0) with time zone);
CREATE TABLE
regression=# insert into bar values(now());
INSERT 155063 1
regression=# select * from bar;
           f1
------------------------
 2004-06-21 21:46:36-04
(1 row)


            regards, tom lane