Re: Any advantage to integer vs stored date w. timestamp

Zoolin Lin wrote:
> Hi,
>
> I have database with a huge amount of data so i'm trying to make it
> as fast as possible and minimize space.
>
> One thing i've done is join on a prepopulated date lookup table to
> prevent a bunch of rows with duplicate date columns. Without this I'd
> have about 2500 rows per hour with the exact same date w. timestamp
> in them.
>
> My question is, with postgres do I really gain anything by this, or
> should I just use the date w. timestamp column on the primary table
> and ditch the join on the date_id table.
>
> Primary table is all integers like:
>
> date id | num1 | num2 | num3 | num4 | num5 | num6 | num7 | num 8
> -------------------------------------------------------------------------------------------------
>  primary key is on date to num->6 columns

What types are num1->8?

> date_id lookup table:
>
> This table is prepopulated with the date values that will be used.
>
> date_id | date w timestamp ---------------------------------------- 1
> | 2007-2-15 Midnight 2         | 2007-2-15 1 am 3         | 2007-2-15
> 2 am  etc for 24 hours each day

If you only want things accurate to an hour, you could lost the join and
just store it as an int: 2007021500, 2007021501 etc.

That should see you good to year 2100 or so.

--
   Richard Huxton
   Archonet Ltd