Re: timestamps, formatting, and internals

On 05/30/2012 01:48 PM, David Salisbury wrote:
>
>
> On 5/30/12 9:42 AM, Adrian Klaver wrote:
>> 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! I was looking for some sort of verification along these lines.
> So in my mind, the internal storage of a timestamp would be the number
> of milliseconds since 1970 ( or similar ). But to me, if I cast something
> that is an integer into an integer it would still be an integer ;) , and
> still hold the milliseconds. Perhaps if I cast a datetime into a bigint
> it'll
> still hold the number of ms? Some sort of parameter setting for dates
> would be nice to be able to default a date/time format down to the ms, w/o
> having to explicitly format it with every select... imho

The client does not see the internal value. That value is decoded when
presented to the client and encoded when the client presents a datetime
value. Any integer casting you do as the client will not change that. As
to datetime formatting, I believe that was covered in another thread:)

test=> SELECT now();
now
-------------------------------
2012-05-30 14:25:28.719475-07
(1 row)

test=> SELECT '2012-05-30'::TIMESTAMP;
timestamp
---------------------
2012-05-30 00:00:00
(1 row)

test=> SELECT '2012-05-30 2:26:45.56789'::TIMESTAMP;
timestamp
---------------------------
2012-05-30 02:26:45.56789
(1 row)

A timestamp will return values that are significant.

>
> -ds

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