Thread: data storage for timestamp data type?

The timestamp data type in postgreSQL has the option for a parameter 'p' that defines the precision from 0-6. The documentation indicates 8 bytes as the default size for the column.

If I use timestamp(3) as an example, will postgres still allocate the entire 8 bytes for the column or reduce it?

Thanks!
Chirag

PS. By the way, I think clustering question hit the list 3 times by accident last week. I apologize, I used the wrong e-mail address and assumed the first 2 didn't make it.

On Aug 14, 2007, at 13:52 , Chirag Patel wrote:

> If I use timestamp(3) as an example, will postgres still allocate
> the entire 8 bytes for the column or reduce it?

8 bytes will still be allocated. Internally it's an 8-byte int or an
8-byte float depending on whether or not you compiled with enable-
integer-datetimes.

Michael Glaesemann
grzm seespotcode net

[Please don't top post as it makes the discussion more difficult to
follow, and please reply to the list so that others may benefit from
and participate in the discussion.]

On Aug 14, 2007, at 14:38 , Chirag Patel wrote:

> Do you know how PostgreSQL stores the timestamp? The documentation
> indicates the following:
>
>         "timestamp values are stored as seconds before or after
> midnight 2000-01-01"
>
> I understand that PostgreSQL accepts the timestamp in string format
> ('2003-07-29 13:19:30.5'). But how is it actually stored? For
> example, if I use timestamp(3), then is it total number of
> milliseconds since "midnight 2000-01-01"?

Again, it depends on whether or not the server was compiled with
enable-integer-datetimes. If so, it's stored as microseconds from
midnight 2000-01-01 (in an int8). If not, it's stored as seconds from
midnight 2000-01-01 (in a float8).

Michael Glaesemann
grzm seespotcode net