Postgres storing time in strange manner

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort of
behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

--
Rod Taylor

On Sunday 15 September 2002 10:11 am, Rod Taylor wrote:

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort
of behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

How then, am I supposed to explain to a web interface user that when they just
entered 5:00:00, it's going to sometimes show up to 4:59:60?

I'm entering an exact timestamp, that being 5:00:00. Regardless of how many
seconds you claim were in the former minute, it should not subtract a second
from my entry, because 5:00:00 by your definition would mean 4:59 and 61
seconds.

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Dance like nobody's watching.

On Sunday 15 September 2002 10:11 am, Rod Taylor wrote:

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort
of behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

True but irrelevant -- PG does not do accounting for leap seconds.

The roundoff bug Casey is looking at is fixed in recent PG versions;
I'd recommend an update to 7.2.2.

regards, tom lane

How then, am I supposed to explain to a web interface user that when they just
entered 5:00:00, it's going to sometimes show up to 4:59:60?

Oh, I see. I thought you were doing some interval math on it. Yes,
your right, it should be taken as being 5:00:00.

I'm entering an exact timestamp, that being 5:00:00. Regardless of how many
seconds you claim were in the former minute, it should not subtract a second
from my entry, because 5:00:00 by your definition would mean 4:59 and 61
seconds.

Either way, I've been unable to reproduce it with either 7.2 or 7.3 --
nor do I see any notes about that feature having been removed or carried
over to current releases -- no regression tests for it in 7.3.

Could you provide a complete test case, or confirm that it does what you
expect in 7.3?

7.2.2:
iqdb=# select '0001-01-01 4:59:60'::timestamptz;
ERROR: Bad timestamp external representation '0001-01-01 4:59:60'

--
Rod Taylor

On Sun, 2002-09-15 at 10:32, Tom Lane wrote:

On Sunday 15 September 2002 10:11 am, Rod Taylor wrote:

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort
of behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

True but irrelevant -- PG does not do accounting for leap seconds.

For some reason I thought it did accommodate it when I was still using
7.1.

Ahh well, if the bug is fixed, then it's all good.

--
Rod Taylor

On Sunday 15 September 2002 10:32 am, Tom Lane wrote:

The roundoff bug Casey is looking at is fixed in recent PG versions;
I'd recommend an update to 7.2.2.

I'm running a freshly compiled version of 7.2.2

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Dance like nobody's watching.

Casey Allen Shobe <cshobe@secureworks.net> writes:

On Sunday 15 September 2002 10:32 am, Tom Lane wrote:

The roundoff bug Casey is looking at is fixed in recent PG versions;
I'd recommend an update to 7.2.2.

I'm running a freshly compiled version of 7.2.2

Oh? On what platform?

regards, tom lane

The kernel version is probably not relevant here; what's more
interesting is the compiler version, compiler optimization level,
and perhaps libc version.

cshobe@gabrielle:/www/htdocs$ gcc -v
Reading specs from /usr/lib/gcc-lib/i686-pc-linux-gnu/2.95.3/specs
gcc version 2.95.3 20010315 (release)
cshobe@gabrielle:/www/htdocs$ echo $CFLAGS
-O3 -march=i686 -mcpu=i686 -funroll-loops -ffast-math

Bingo: it's the -ffast-math option that's causing the problem.
See, eg,
http://fts.postgresql.org/db/mw/msg.html?mid=1277557

If you rebuild without that, I think you'll find the problem goes away.

ISTM that at one time we had considered actively discriminating against
-ffast-math in CFLAGS in configure ... but I don't see any sign in
current sources that we make any attempt to remove -ffast-math from
environment-supplied CFLAGS. Peter, do you think that would be a
reasonable thing to do?

regards, tom lane

On Sunday 15 September 2002 02:14 pm, Tom Lane wrote:

Bingo: it's the -ffast-math option that's causing the problem.
See, eg,
http://fts.postgresql.org/db/mw/msg.html?mid=1277557

Thank you, I hadn't realized that they shouldn't be used together...I've used
them for every package on my box :\...ah well, live and learn.

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Dance like nobody's watching.

Casey,

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

How then, am I supposed to explain to a web interface user that when they just
entered 5:00:00, it's going to sometimes show up to 4:59:60?

Leap minutes? Oh please. I'm gonna have to account for green martians
next...

DSL
--
Con te partiro, su navi per mari
Che io lo so, no, no non esistono piu
Con te io li vivro.
(Sartori F, Quarantotto E)

On Sun, 2002-09-15 at 17:15, David Lloyd wrote:

Casey,

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

How then, am I supposed to explain to a web interface user that when they just
entered 5:00:00, it's going to sometimes show up to 4:59:60?

Leap minutes? Oh please. I'm gonna have to account for green martians
next...

Well, there *are* leap minutes and leap seconds. They just happen
*so* rarely...

-- 
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

Tom Lane writes:

ISTM that at one time we had considered actively discriminating against
-ffast-math in CFLAGS in configure ... but I don't see any sign in
current sources that we make any attempt to remove -ffast-math from
environment-supplied CFLAGS. Peter, do you think that would be a
reasonable thing to do?

Would it be possible to write a short test case that exhibits this
behavior?

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

ISTM that at one time we had considered actively discriminating against
-ffast-math in CFLAGS in configure ... but I don't see any sign in
current sources that we make any attempt to remove -ffast-math from
environment-supplied CFLAGS. Peter, do you think that would be a
reasonable thing to do?

Would it be possible to write a short test case that exhibits this
behavior?

Not sure; apparently it depends on optimization level, so I'd be
hesitant to assume that any short test case would reliably expose
the problem. Also, aren't you trying to avoid run-time tests in
configure?

But if you prefer a run-time test, I'll see if I can cons one up.

regards, tom lane

Tom Lane writes:

Not sure; apparently it depends on optimization level, so I'd be
hesitant to assume that any short test case would reliably expose
the problem. Also, aren't you trying to avoid run-time tests in
configure?

If the test doesn't use any library function's run-time behavior, you can
usually do something like

main() {
int a[(2.0+2.0==4.0)?1:-1]
}

This will fail to compile if the floating-point arithmetic is broken.

Otherwise a good solution might be to print a warning if configure detects
the flag. Or we can strip it out unconditionally, but that seems wrong.
The user should be made aware of the problem.

--
Peter Eisentraut peter_e@gmx.net

On Sun, 2002-09-15 at 09:51, Rod Taylor wrote:

On Sun, 2002-09-15 at 10:32, Tom Lane wrote:

On Sunday 15 September 2002 10:11 am, Rod Taylor wrote:

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort
of behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

There are actually 61 seconds in some minutes. In order to accommodate
leap seconds, PostgreSQL allows this to happen -- similarly to how it
will also allow 366 days in some years.

True but irrelevant -- PG does not do accounting for leap seconds.

For some reason I thought it did accommodate it when I was still using
7.1.

Ahh well, if the bug is fixed, then it's all good.

In a subsequent post, Tom Lane said that 7.2.2 fixed the problem,
but a reply post from the original poster says that he is, in fact,
using 7.2.2...

-- 
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

Ron Johnson <ron.l.johnson@cox.net> writes:

On Sun, 2002-09-15 at 03:57, Casey Allen Shobe wrote:

I don't have a clue why it's doing this - has anyone else seen this sort
of behavior, or know why it might be doing it? It shows times wrong, for
instance, it shows 00:04:60 where it should show 00:05:00. See below:

In a subsequent post, Tom Lane said that 7.2.2 fixed the problem,
but a reply post from the original poster says that he is, in fact,
using 7.2.2...

Further investigation showed that he'd built Postgres with -ffast-math
gcc option, which is well known to break the datetime rounding code :-(

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

If the test doesn't use any library function's run-time behavior, you can
usually do something like

main() {
int a[(2.0+2.0==4.0)?1:-1]
}

This will fail to compile if the floating-point arithmetic is broken.

However, unless gcc itself is compiled with -ffast-math, such an
approach won't show up the bug.

I had success with this test:

#include <stdio.h>

double d18000 = 18000.0;

main() {
int d = d18000 / 3600;
printf("18000.0 / 3600 = %d\n", d);
return 0;
}

Using Red Hat 7.2's compiler:

[tgl@rh1 tgl]$ gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)

I get:

[tgl@rh1 tgl]$ gcc bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 5 -- right
[tgl@rh1 tgl]$ gcc -ffast-math bug.c
[tgl@rh1 tgl]$ ./a.out
18000.0 / 3600 = 4 -- wrong!

You need the dummy global variable to keep the compiler from simplifying
the division at compile time, else you get 5. With the test as
exhibited, the -O level seems not to matter.

regards, tom lane

I said:

I had success with this test:

BTW, some digging in the assembly code shows that the actual problem is
this: instead of emitting "x / 3600.0", with -ffast-math the compiler
emits the equivalent of "x * (double) (1.0 / 3600.0)". It's the
last-bit inaccuracy of the latter constant that's killing us.

regards, tom lane

Ron Johnson <ron.l.johnson@cox.net> writes:

Out of curiosity: why does -ffast-math break the datetime rounding code?

We dug into this last night, and it turns out that the culprit is code
like

int hour = time / 3600;

where time is a double. This yields an exact result when done
correctly, but with -ffast-math gcc will "improve" it to

int hour = time * 0.000277777777777778;

the constant being the nearest double value to 1.0 / 3600.0. The
problem is that the constant is inexact and in fact is slightly too
large; so for example if time is exactly 18000.0, you get a resulting
hour value of 4, not 5, after truncation to integer. Repeated a couple
more times, what should have been 5:00:00 comes out as 4:59:60 ...

regards, tom lane