2

At Postgres documentation page Date/Time Types, it says:

For the time types, the allowed range of p is from 0 to 6 when eight-byte integer storage is used, or from 0 to 10 when floating-point storage is used.

I tried to produce SQL statement with time(p), when p=10? I always get

WARNING: TIME(10) precision reduced to maximum allowed, 6

No matter if I put seconds in float or not. I realize I'm missing something very obvious. Please give an example when p=10.

5
  • Just guessing but 8 bits to a byte so it should be "Int64" or "bigint" in postgres lingo
    – Aliester
    Dec 15, 2016 at 10:25
  • Can you clarify what you don't understand beyond times being represented as 64-bit integers?
    – Blrfl
    Dec 15, 2016 at 11:19
  • @Blrfl updated question
    – Vao Tsun
    Dec 15, 2016 at 11:26
  • 6
    Right above that line that you quoted is a complete description of the difference between integer and floating-point time storage, including the situations where one or the other is used. What didn't you understand from that paragraph?
    – kdgregory
    Dec 15, 2016 at 12:30
  • can you please give an example of sql statement with time(p), when p=10?..
    – Vao Tsun
    Dec 15, 2016 at 14:56

2 Answers 2

4

From the linked page you provide:

Note: When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6. ...

What this says is that in order for timestamp values to be stored as double precision floating-point (and to allow precision up to 10), you need to compile Postgres yourself with that option.

It also says that the precision is then not exactly 10 but it varies depending on the value and may even be less than 6 for some (very high or very low) values.

1
  • thank you for (a deprecated compile-time option) emphasis - at last I got it :)
    – Vao Tsun
    Dec 16, 2016 at 8:03
0

Earlier versions of PostgreSQL didn't appear to store DOUBLE PRECISION values exactly, which was likely the underlying cause of this issue. I was recently working with a colleague who was using PostgreSQL v10 from four years ago.

We found that stored values of file modification timestamps (presented as a float by the stat library call) were frequently retrieved as a slightly imprecise value, ruining the operation of the software.

I'm happy to say that an upgrade to version 14 (I'm currently using 16) fixed the issue, and floats now appear to be stored at full precision.

3
  • I should perhaps have added these are out-of-the-box installations using the brew utility. YMMV.
    – holdenweb
    Dec 21, 2023 at 11:13
  • Are you saying that later versions of Postgres store DOUBLE PRECISION values exactly?
    – mustaccio
    Dec 21, 2023 at 13:16
  • Well I'm not sure you can generalise from my limited experience, but I developed with pgsql 16 and never saw a comparison error. The same software run on pgsql 10 gave comparison errors on most values, of the order of 10e-5. I've drawn my own conclusions. YMMV ;-)
    – holdenweb
    Dec 21, 2023 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.