Which is best, timestamp as float or integer ?
Hi.
From the manual I read that timestamps are stored as double but they
can also be stored as 8 byte integers. I understand the precision
problem with floats and the limited range of the integers and I feel
confident that I should not worry about the Year 294276 or Year
5874897 problems (highest values that can be stored) so I ask for your
experience on this matter when it comes to all other operations.
On the x86-64 platform, I guess that 8 byte integers are generally
handled faster than double floatingpoint numbers? Are operations like
NOW + INTERVAL ' 232 HOURS' also faster with integers? Are there
considerable timesavings?
There are basically two operations I want to do (a lot!)
SELECT ... ORDER BY my_timestamp;
and
SELECT ... WHERE my_timestamp > now(); -- or better using some
variable for the now value to avoid all the function calls.
Btw, what are the words of wisdom when it comes to creating index for
fields of timestamp type, or other clever things?
Best wishes.
On Wednesday 04 February 2009 15:48:41 A B wrote:
From the manual I read that timestamps are stored as double but they
can also be stored as 8 byte integers.
The advantage of the integer storage is mainly that calculations and
comparisons have a predictable error and don't suffer from some of the funny
business that comes with floating-point calculations. Performance is
probably similar. Integer storage is now the default (and has been in some
distributions for a while), and would already have been the sole world order
if 8-byte integer support were universally available.