Thread: Getting milliseconds out of TIMESTAMP
According to the docs, the TIMESTAMP is accurate to microseconds. But the typical display only goes to centiseconds. Java and Unix timestamps generally go to milliseconds.
Is there a way to get Postgresql 7.1 to report times with higher resolution?
Thanks,
Thanks,
David
Hello, Below is a press release of the new YellowDog 2.0. They are including LXP which is a PostgreSQL Application Server. LXP comes with the fingerless content manager and is written in C++. It is run as an Apache Module and has native fallback to PHP. --- Loveland, CO -- 19 April 2001 -- Terra Soft Solutions, Inc., the leading developer of Linux solutions for PowerPC(tm) microprocessors, is pleased to include LXP, Command Prompt's PostgreSQL application server with the much anticipated release of Yellow Dog Linux 2.0. Joshua Drake, Co-Founder of Command Prompt, Inc. states, "Terra Soft Solutions YDL 2.0 is an exceptional distribution that will help increase the viability of Linux and LXP. It is our pleasure to include our LXP application server with their distribution." The LXP application server provides easy access to the advanced features of PostgreSQL. LXP offers a suite of services to assist the Linux web developer produce easily managed, dynamic websites, including: direct fallback to the PHP language, persistent query execution, data parsing, and XML and content management. An example of LXP can be found at LinuxPorts.Com "In addition to the nearly complete YDL 2.0 book, we are pleased to expand the function of Yellow Dog Linux with Command Prompt's quality product. While it is our goal to take YDL 2.0 into the hands of those newer to Linux, LXP adds to the server and development OS foundation we have built with Champion Server," states Kai Staats, CEO of Terra Soft Solutions, Inc. About Command Prompt, Inc. Command Prompt, Inc., is a Linux company specializing in custom application development and technical writing, with emphasis on delivering quality products and open source solutions to the business marketplace. Command Prompt is best known for its LinuxPorts.Com portal and OpenDocs Publishing technical publishing company. For more information, visit: CommandPrompt.Com About Terra Soft Solutions, Inc. Terra Soft Solutions, Inc. is a leading developer of innovative technologies for PowerPC Linux. Yellow Dog Linux, their flagship product, has boosted viability of Linux for PowerPC. Black Lab Linux provides an advanced Linux distribution for parallel and embedded computing systems. For more information, visit Terra Soft Contact: Joshua Drake Command Prompt, Inc. 503-736-4609, http://www.commandprompt.com/ -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
David Wall wrote: > > According to the docs, the TIMESTAMP is accurate to microseconds. But the = > typical display only goes to centiseconds. Java and Unix timestamps genera= > lly go to milliseconds. > > Is there a way to get Postgresql 7.1 to report times with higher resolution? > > Thanks, > David > Just curious, but what is the point of having times 'acurate' to the milisecond in a database? Nils
> Just curious, but what is the point of having times 'acurate' to the > milisecond in a database? In my case it's a simple matter that I include a timestamp in a digital signature, and since my timestamp comes from Java (and most Unixes are the same), it has millisecond resolution "built in." The problem is that when I retrieve the information about that digital signature, it was failing because the database only when to centiseconds. I've "fixed" my code by reducing the my timestamp resolution. As another point, computers are incredibly fast these days, and doing more than 100 logging operations in a second is commonplace. If you log records to the database and do more than 100/second, then you cannot use the TIMESTAMP as an indicator of order that messages were emitted since all rows logged after the 100th will be the same. Of course, the question is easy to turn around. Why not just have timestamps accurate to the second? Or perhaps to the minute since many (most?) computer clocks are not that accurate anyway? The real question for me is that 7.1 docs say that the resolution of a timestamp is 8 bytes at "1 microsecond / 14 digits", yet I generally see YYYY-MM-DD HH-MM-SS.cc returned in my queries (both with pgsql and with JDBC). This is unusual since an 8 byte integer is 2^63, which is far more than 14 digits, and the "ascii digits" of that preceding format is already 16 digits. David
"David Wall" <d.wall@computer.org> writes:
> The real question for me is that 7.1 docs say that the resolution of a
> timestamp is 8 bytes at "1 microsecond / 14 digits", yet I generally see
> YYYY-MM-DD HH-MM-SS.cc returned in my queries (both with pgsql and with
> JDBC).
That's just a matter of the default display format not being what you
want. The underlying representation is double precision seconds from
(IIRC) 1/1/2000, so accuracy is 1 microsec or better for ~70 years
either way from that date, decreasing as you move further out.
One way to get the fractional seconds with better precision is
date_part. For example,
regression=# create table ts (f1 timestamp);
CREATE
regression=# insert into ts values(now());
INSERT 144944 1
regression=# insert into ts values(now() + interval '.0001 sec');
INSERT 144945 1
regression=# insert into ts values(now() + interval '.000001 sec');
INSERT 144946 1
regression=# insert into ts values(now() + interval '.0000001 sec');
INSERT 144947 1
regression=# select f1, date_part('epoch', f1), date_part('microseconds', f1) from ts;
f1 | date_part | date_part
---------------------------+------------------+-------------------
2001-04-22 16:04:31-04 | 987969871 | 0
2001-04-22 16:04:39.00-04 | 987969879.0001 | 100.00000000332
2001-04-22 16:04:45.00-04 | 987969885.000001 | 0.999999997475243
2001-04-22 16:04:51-04 | 987969891 | 0
(4 rows)
Not sure why the last example drops out completely --- looks like
something is rounding off sooner than it needs to. But certainly there
are six fractional digits available at the moment.
regards, tom lane
> "David Wall" <d.wall@computer.org> writes:
> > The real question for me is that 7.1 docs say that the resolution of a
> > timestamp is 8 bytes at "1 microsecond / 14 digits", yet I generally see
> > YYYY-MM-DD HH-MM-SS.cc returned in my queries (both with pgsql and with
> > JDBC).
>
> That's just a matter of the default display format not being what you
> want. The underlying representation is double precision seconds from
> (IIRC) 1/1/2000, so accuracy is 1 microsec or better for ~70 years
> either way from that date, decreasing as you move further out.
That makes sense, but it wasn't clear if there was a clean way to get the
extra info. Your example below looks interesting, but I'll need to look
further to see if I understand what you are really doing. The real key for
me will be to come up with a generic mechanism that is easy to plug into
JDBC.
> One way to get the fractional seconds with better precision is
> date_part. For example,
>
> regression=# create table ts (f1 timestamp);
> CREATE
> regression=# insert into ts values(now());
> INSERT 144944 1
> regression=# insert into ts values(now() + interval '.0001 sec');
> INSERT 144945 1
> regression=# insert into ts values(now() + interval '.000001 sec');
> INSERT 144946 1
> regression=# insert into ts values(now() + interval '.0000001 sec');
> INSERT 144947 1
> regression=# select f1, date_part('epoch', f1), date_part('microseconds',
f1) from ts;
> f1 | date_part | date_part
> ---------------------------+------------------+-------------------
> 2001-04-22 16:04:31-04 | 987969871 | 0
> 2001-04-22 16:04:39.00-04 | 987969879.0001 | 100.00000000332
> 2001-04-22 16:04:45.00-04 | 987969885.000001 | 0.999999997475243
> 2001-04-22 16:04:51-04 | 987969891 | 0
> (4 rows)
>
> Not sure why the last example drops out completely --- looks like
> something is rounding off sooner than it needs to. But certainly there
> are six fractional digits available at the moment.
This solution appears to show that the data is there, but that extracting it
is not that pleasant, especially in an automated way from JDBC. It seems
like we'd have to modify select calls such that whenever a timestamp field
is being used, we'd get the timestamp, but also get the 'date_part(epoch)',
then put use the numbers after the decimal point in epoch and replace any
numbers after the period in the timestamp.
If I have to change my code to make it work, then I'd prefer to simply store
the 64-bit long integer in the database and save all of the date conversions
and parsing. But when it's a TIMESTAMP, the data looks a lot better when
using psql, and it makes queries by date ranges usable by mortals!
Thanks,
David