date&time vs timestamp question

From: "Ron Mayer" <ron(at)intervideo(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: date&time vs timestamp question
Date: 2003-04-23 22:34:32
Message-ID: POEDIPIPKGJJLDNIEMBEIEBLCKAA.ron@intervideo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


I've been asked what the best way for storing dates&times 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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2003-04-23 22:56:13 Re: date&time vs timestamp question
Previous Message Tom Lane 2003-04-23 20:33:39 Re: ERROR: XLogFlush: