By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Timestamp precision and rounding

P: n/a
(asked last week on .questions, no response)

Can anyone explain why this happens? (under 7.4.1)

select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:01
select '2004-05-27 09:00:00.500000-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:00
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?

--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

On Thu, 3 Jun 2004, Jeff Boes wrote:
(asked last week on .questions, no response)

Can anyone explain why this happens? (under 7.4.1)

select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:01
select '2004-05-27 09:00:00.500000-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:00
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?


My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a

Stephan Szabo <ss****@megazone.bigpanda.com> writes:
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?
My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?


Fwiw, the floating point timestamp representation is seconds-based. So 0.5s
should be exactly representable. (Though 0.500001 wouldn't, but that shouldn't
matter.)

On my machine it seems to always round away from 0, but this comment from
timestamp.c seems relevant. It would imply my build was build with integer
timestamps and yours was built with floating point timestamps:

/*
* Note: this round-to-nearest code is not completely consistent
* about rounding values that are exactly halfway between integral
* values. On most platforms, rint() will implement
* round-to-nearest-even, but the integer code always rounds up
* (away from zero). Is it worth trying to be consistent?
*/

And this is from the glibc Info page:
IEEE 754 defines four possible rounding modes:

Round to nearest.
This is the default mode. It should be used unless there is a
specific need for one of the others. In this mode results are
rounded to the nearest representable value. If the result is
midway between two representable values, the even representable is
chosen. "Even" here means the lowest-order bit is zero. This
rounding mode prevents statistical bias and guarantees numeric
stability: round-off errors in a lengthy calculation will remain
smaller than half of `FLT_EPSILON'.


[And the other rounding directions are useless;
this is the default and the only one that matters.]

--
greg
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

P: n/a

On Thu, 3 Jun 2004, Jeff Boes wrote:
(asked last week on .questions, no response)

Can anyone explain why this happens? (under 7.4.1)

select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:01
select '2004-05-27 09:00:00.500000-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:00
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?


My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

P: n/a

Stephan Szabo <ss****@megazone.bigpanda.com> writes:
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?
My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?


Fwiw, the floating point timestamp representation is seconds-based. So 0.5s
should be exactly representable. (Though 0.500001 wouldn't, but that shouldn't
matter.)

On my machine it seems to always round away from 0, but this comment from
timestamp.c seems relevant. It would imply my build was build with integer
timestamps and yours was built with floating point timestamps:

/*
* Note: this round-to-nearest code is not completely consistent
* about rounding values that are exactly halfway between integral
* values. On most platforms, rint() will implement
* round-to-nearest-even, but the integer code always rounds up
* (away from zero). Is it worth trying to be consistent?
*/

And this is from the glibc Info page:
IEEE 754 defines four possible rounding modes:

Round to nearest.
This is the default mode. It should be used unless there is a
specific need for one of the others. In this mode results are
rounded to the nearest representable value. If the result is
midway between two representable values, the even representable is
chosen. "Even" here means the lowest-order bit is zero. This
rounding mode prevents statistical bias and guarantees numeric
stability: round-off errors in a lengthy calculation will remain
smaller than half of `FLT_EPSILON'.


[And the other rounding directions are useless;
this is the default and the only one that matters.]

--
greg
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5

P: n/a
Stephan Szabo wrote:
On Thu, 3 Jun 2004, Jeff Boes wrote:
(asked last week on .questions, no response)

Can anyone explain why this happens? (under 7.4.1)

select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:01
select '2004-05-27 09:00:00.500000-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:00
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?


My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?

2004-05-27 09:00:02, so I guess that would confirm it.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6

P: n/a
Stephan Szabo wrote:
On Thu, 3 Jun 2004, Jeff Boes wrote:
(asked last week on .questions, no response)

Can anyone explain why this happens? (under 7.4.1)

select '2004-05-27 09:00:00.500001-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:01
select '2004-05-27 09:00:00.500000-04' :: timestamp(0) ;

timestamp
---------------------
2004-05-27 09:00:00
That is, why doesn't the second operation result in the same timestamp
as the first? Is it a floating-point representation issue, or are the
mathematical rules of rounding not being followed correctly (as I
understand them, anyway)?


My first guess would be that your system probably implements its default
rounding as nearest even for .5 results, what does 9:00:01.5 give you?

2004-05-27 09:00:02, so I guess that would confirm it.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.