Re: [HACKERS] [PATCH] Fix for documentation of timestamp type
On Tue, Dec 13, 2016 at 10:46 AM, Robert Haaswrote: > On Tue, Dec 13, 2016 at 10:41 AM, Tom Lane wrote: >> Robert Haas writes: >>> I find this a bit unclear, because the revised text kind of jumps back >>> and forth between the floating-point and integer formats. Perhaps >>> something like this: >> >> Your wording seems OK to me, although I'd drop the "instead". > > Good idea. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Fix for documentation of timestamp type
The latest patch attachment has a couple typos in it ("storead" instead of "stored"). I interpreted the final suggestion in the thread to mean 1) default stores in microseconds 2) deprecated compile-time option stores as seconds. If these assumptions are correct then the suggestion in the thread (minus "instead" as Tom suggested) provided below should be incorporated and attached as a patch to this thread. Therefore I recommend an "Awaiting Author" status. When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. In this case, the internal representation is the number of microseconds before or after midnight 2000-01-01. When timestamp values are stored as double precision floating-point numbers (a deprecated compile-time option), the internal representation is the number of seconds before or after midnight 2000-01-01. With this representation, the effective limit of precision might be less than 6; in practice, microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of timestamp values to be represented than shown above: from 4713 BC up to 5874897 AD. Thanks, -Cynthia -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Fix for documentation of timestamp type
On Tue, Dec 13, 2016 at 10:41 AM, Tom Lanewrote: > Robert Haas writes: >> I find this a bit unclear, because the revised text kind of jumps back >> and forth between the floating-point and integer formats. Perhaps >> something like this: > > Your wording seems OK to me, although I'd drop the "instead". Good idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Fix for documentation of timestamp type
Robert Haaswrites: > I find this a bit unclear, because the revised text kind of jumps back > and forth between the floating-point and integer formats. Perhaps > something like this: Your wording seems OK to me, although I'd drop the "instead". regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Fix for documentation of timestamp type
On Mon, Dec 12, 2016 at 8:50 AM, Aleksander Alekseevwrote: > 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. I find this a bit unclear, because the revised text kind of jumps back and forth between the floating-point and integer formats. Perhaps something like this: When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. In this case, the internal representation is the number of microseconds before or after midnight 2000-01-01. When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the internal representation is the number of seconds before or after midnight 2000-01-01. With this representation, the effective limit of precision might be less than 6; in practice, microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of timestamp values to be represented than shown above: from 4713 BC up to 5874897 AD. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Fix for documentation of timestamp type
Hello. Currently doc/src/sgml/datatype.sgml states: ``` 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. timestamp 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 00 00 00 00 08 13 10 03 00 00 00 00 1c 00 e0 1f 0010 00 20 04 20 00 00 00 00 e0 9f 40 00 00 00 00 00 0020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 * 1fe0 3c 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 1ff0 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 diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 67d0c34..01a8492 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1654,10 +1654,11 @@ SELECT E'\\xDEADBEEF'; 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. timestamp values are stored as -seconds before or after midnight 2000-01-01. When +might be less than 6. By default timestamp values are +storead as microseconds before or after midnight 2000-01-01. When timestamp values are implemented using floating-point -numbers, microsecond precision is achieved for dates within a few +numbers, values are storead as number of seconds. In this case +microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of timestamp values to be represented than signature.asc Description: PGP signature