Re: timestamps, formatting, and internals

On 05/29/2012 07:08 PM, Adrian Klaver wrote:
> On 05/29/2012 04:28 PM, David Salisbury wrote:
>>
>>
>> On 5/27/12 12:25 AM, Jasen Betts wrote:
>>> The query: "show integer_datetimes;" should return 'on' which means
>>> timestamps are microsecond precision if it returns 'off' your database
>>> was built with floating point timstamps and equality tests will be
>>> unreliable,
>>
>> I find that rather interesting. I was told that I was losing microseconds
>> when I extracted an epoch from the difference between two timestamps and
>> casted
>> that value to an integer. So if I have integer timestamps ( your case
>> above )
>> I get microseconds, but integer epochs is without microseconds?
>

Think I realize where the confusion is now. When Jasen mentioned integer
datetimes he was referring to the internal storage format Postgres uses
to record the datetime value. Via the magic of programming(others will
have to fill that part in) the internal format can represent time down
to microseconds even though the value is actually stored as an
eight-byte integer. When you do an explicit cast of a timestamp value to
integer you are asking that the value be only a whole number and the
decimal portion is discarded. In other words the internal integer
encodes the decimal values the external integer does not.

>>
>> Thanks,
>>
>> -ds
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com