Skip site navigation (1) Skip section navigation (2)

[PATCH] Fix for documentation of timestamp type

From: Aleksander Alekseev <a(dot)alekseev(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [PATCH] Fix for documentation of timestamp type
Date: 2016-12-12 13:50:46
Message-ID: 20161212135045.GB15488@e733.localdomain (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
Hello.

Currently doc/src/sgml/datatype.sgml states:

```
    When <type>timestamp</> values are stored as eight-byte integers
    (currently the default), microsecond precision is available over 
    the full range of values. When <type>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. <type>timestamp</type> values are stored as
    seconds before or after midnight 2000-01-01. [...]
```

It gives a wrong impression that by default timestamp is stored as a
number of seconds after midnight 2000-01-01 in a eight-byte integer. In
fact timestamp is stored in MICROseconds, not seconds. For instance,
2016-12-12 16:03:14.643886 is represented as number 534873794643886:

```
$ echo "select relfilenode from pg_class where relname = 'tst';" | psql
 relfilenode 
-------------
       16431
(1 row)

$ find /path/to/data/dir -type f -name 16431
[...]

$ hexdump -C path/to/found/table/segment
00000000  00 00 00 00 08 13 10 03  00 00 00 00 1c 00 e0 1f
00000010  00 20 04 20 00 00 00 00  e0 9f 40 00 00 00 00 00
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00
*
00001fe0  3c 02 00 00 00 00 00 00  00 00 00 00 00 00 00 00
00001ff0  01 00 01 00 00 09 18 00  ae 87 87 02 77 e6 01 00

$ python
>>> "{:x}".format(534873794643886)
'1e677028787ae'
```

'ae 87 87 02 77 e6 01 00' is exactly what is physically stored on disk.
You can calculate current year from number 534873794643886 like this:

```
>>> int(2000 + 534873794643886 / 1000 / 1000 / 60 / 60 / 24 / 365.2425)
2016
```

I suggest to rewrite the documentation a bit to make it more clear that
by default timestamp is stored in microseconds. Corresponding patch is
attached.

-- 
Best regards,
Aleksander Alekseev

Attachment: timestamp-documentation-fix-v1.patch
Description: text/x-diff (1.1 KB)

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2016-12-12 13:54:13
Subject: Re: background sessions
Previous:From: Andrew DunstanDate: 2016-12-12 13:40:35
Subject: Re: jacana hung after failing to acquire random number

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group