How does Postgresql round half-microseconds in timestamps?

Question

I was under the impression that PostgreSQL rounded half-microseconds in timestamps to the nearest even microsecond. E.g.:

> select '2000-01-01T00:00:00.0000585Z'::timestamptz;
          timestamptz          
-------------------------------
 2000-01-01 01:00:00.000058+01
(1 row)

> select '2000-01-01T00:00:00.0000575Z'::timestamptz;
          timestamptz          

-------------------------------
 2000-01-01 01:00:00.000058+01
(1 row)

Then I discovered that:

> select '2000-01-01T00:00:00.5024585Z'::timestamptz;
          timestamptz          
-------------------------------
 2000-01-01 01:00:00.502459+01
(1 row)

Does anybody know the rounding algorithm Postgresql uses for timestamps?

For your information, here's the version of Postgresql I'm running:

> select version();  
                                                version                                                     
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1), 64-bit
(1 row)

Answer 1

All the PostgreSQL time types have a microsecond resolution, six decimal places. Rounding to the nearest even microsecond would not be microsecond resolution.

Its behavior looks consistent with round half-up to me, the usual way to round. >= 0.5 round up, else round down.

0.5024585 rounded half-up to 6 decimal places rounds up to 0.502459 because the 7th digit is 5.

test=# select '2000-01-01T00:00:00.5024585Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502459
(1 row)

0.5024584999999 rounds down to 0.502458 because the 7th digit is 4.

test=# select '2000-01-01T00:00:00.5024584999999Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502458
(1 row)

---

Nevermind, the above appears to be anomalous. Stepping through '2000-01-01T00:00:00.5024235Z' to '2000-01-01T00:00:00.5024355Z' is consistent with half-even rounding.

I'm going to guess the anomalies are due to floating point error converting from floating point seconds in the input to the integer microseconds that timestamp uses.

test=# select '2000-01-01T00:00:00.5024235Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502424
(1 row)

test=# select '2000-01-01T00:00:00.5024245Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502425
(1 row)

test=# select '2000-01-01T00:00:00.5024255Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502425
(1 row)

test=# select '2000-01-01T00:00:00.5024265Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502426
(1 row)

test=# select '2000-01-01T00:00:00.5024275Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502428
(1 row)

test=# select '2000-01-01T00:00:00.5024285Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502428
(1 row)

test=# select '2000-01-01T00:00:00.5024295Z'::timestamp;
         timestamp         
---------------------------
 2000-01-01 00:00:00.50243
(1 row)

test=# select '2000-01-01T00:00:00.5024305Z'::timestamp;
         timestamp         
---------------------------
 2000-01-01 00:00:00.50243
(1 row)

test=# select '2000-01-01T00:00:00.5024315Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502432
(1 row)

test=# select '2000-01-01T00:00:00.5024325Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502432
(1 row)

test=# select '2000-01-01T00:00:00.5024335Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502434
(1 row)

test=# select '2000-01-01T00:00:00.5024345Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502434
(1 row)

test=# select '2000-01-01T00:00:00.5024355Z'::timestamp;
         timestamp          
----------------------------
 2000-01-01 00:00:00.502436
(1 row)

This also plays out with interval N microsecond. Less decimal places means less floating point error.

test=# select interval '0.5 microsecond';
 interval 
----------
 00:00:00
(1 row)

test=# select interval '1.5 microsecond';
    interval     
-----------------
 00:00:00.000002
(1 row)

test=# select interval '2.5 microsecond';
    interval     
-----------------
 00:00:00.000002
(1 row)

test=# select interval '3.5 microsecond';
    interval     
-----------------
 00:00:00.000004
(1 row)

test=# select interval '4.5 microsecond';
    interval     
-----------------
 00:00:00.000004
(1 row)

test=# select interval '5.5 microsecond';
    interval     
-----------------
 00:00:00.000006
(1 row)

test=# select interval '6.5 microsecond';
    interval     
-----------------
 00:00:00.000006
(1 row)

A small C program confirms there's a floating point accuracy problem with single precision floats at 7 decimal places that would affect rounding.

#include <math.h>
#include <stdio.h>

int main() {
    float nums[] = {
        0.5024235f,
        0.5024245f,
        0.5024255f,
        0.5024265f,
        0.5024275f,
        0.5024285f,
        0.5024295f,
        0.5024305f,
        NAN
    };

    for( int i = 0; !isnan(nums[i]); i++ ) {
        printf("%0.8f\n", nums[i]);
    }
}

This produces:

0.50242352
0.50242448
0.50242549
0.50242651
0.50242752
0.50242847
0.50242949
0.50243050

Whereas with doubles, there's no problem.

0.50242350
0.50242450
0.50242550
0.50242650
0.50242750
0.50242850
0.50242950
0.50243050