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.

  • Just guessing but 8 bits to a byte so it should be "Int64" or "bigint" in postgres lingo
    – Aliester
    Dec 15 '16 at 10:25
  • Can you clarify what you don't understand beyond times being represented as 64-bit integers?
    – Blrfl
    Dec 15 '16 at 11:19
  • @Blrfl updated question
    – Vao Tsun
    Dec 15 '16 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 '16 at 12:30
  • can you please give an example of sql statement with time(p), when p=10?..
    – Vao Tsun
    Dec 15 '16 at 14:56

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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