Thread: Getting milliseconds out of TIMESTAMP

Getting milliseconds out of TIMESTAMP

From
"David Wall"
Date:
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,
David

Terra Soft to Include LXP with YDL 2.0

From
Poet/Joshua Drake
Date:
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.
--


Re: Getting milliseconds out of TIMESTAMP

From
Nils Zonneveld
Date:

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

Re: Re: Getting milliseconds out of TIMESTAMP

From
"David Wall"
Date:
> 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


Re: Re: Getting milliseconds out of TIMESTAMP

From
Tom Lane
Date:
"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

Re: Re: Getting milliseconds out of TIMESTAMP

From
"David Wall"
Date:
> "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