Re: timestamps, formatting, and internals

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.

-ds