Re: timestamps, formatting, and internals

On 2012-05-30, David Salisbury <salisbury(at)globe(dot)gov> 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.

It's internally stored as int8, but treated arithmetically as a number
of millionths. "Fixed point" is the apropiate term I think.

> Perhaps if I cast a datetime into a bigint it'll
> still hold the number of ms?

only if you multiply it by 1000000

> 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.

sounds like a potential foot-gun to me.

--
⚂⚃ 100% natural