Which is best, timestamp as float or integer ?

From: A B <gentosaker(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Which is best, timestamp as float or integer ?
Date: 2009-02-04 13:48:41
Message-ID: dbbf25900902040548g445706aeuaa2f80a64fae6244@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2009-02-04 14:08:30 Re: Crash of Postgresql on Windows
Previous Message Emilie Laffray 2009-02-04 13:43:14 Re: Crash of Postgresql on Windows