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× 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
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: |