Re: timestamps, formatting, and internals - Mailing list pgsql-general

From Jasen Betts
Subject Re: timestamps, formatting, and internals
Date
Msg-id jqce1u$puj$3@reversiblemaps.ath.cx
Whole thread Raw
In response to timestamps, formatting, and internals  (David Salisbury <salisbury@globe.gov>)
List pgsql-general
On 2012-05-30, David Salisbury <salisbury@globe.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

pgsql-general by date:

Previous
From: Jasen Betts
Date:
Subject: Re: Disable Streaming Replication without restarting either master or slave
Next
From: Jasen Betts
Date:
Subject: Re: Updateable Views or Synonyms.