I've been asked what the best way for storing dates× in a postgresql database.
Anyone have a summary of the advantages/disadvantages of
create table a (dat date, tim time, ...);
vs.
create table b (dattim timestamp, ...);
The table will be kinda large, perhaps 10 million rows, and
many queries would be searching creating aggregate information
of events that happened on a particular day. Perhaps
select min(price),thing from table where dat='2002-01-01' group by thing;
will be a typical query. Sometimes but rarely will aggregate queries want
sub-day resolution, but I need the time because the total order is important.
In my mind..
Advantages for a separate date, time:
* somewhat nicer query syntax...
select whatever from a where dat='2003-01-01';
vs
select whatever from a where dattim>='2003-01-01' and dattim<'2003-01-02;
or
select whatever from a where date_trunc('day',dattim)='2003-01-01'
* Could analyze generate have meaningful statistics
with a separate date column for the optimizer? For example, the
most-common values in pg_stats is probably less useful for timestamps
than dates.
Advantages for a combined timestamp:
* looks like a few bytes smaller.
* slightly easier to isolate a specific event based on time.
Any other advantages/disadvantages/opinions welcome.
Ron
