date&time vs timestamp question
| От | Ron Mayer |
|---|---|
| Тема | date&time vs timestamp question |
| Дата | |
| Msg-id | POEDIPIPKGJJLDNIEMBEIEBLCKAA.ron@intervideo.com обсуждение |
| Ответ на | Re: postgresql-7.3 implementation (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: date&time vs timestamp question
|
| Список | 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
В списке pgsql-novice по дате отправления: